excel - 如何使用现有字符串将 "yes/no"正确添加到 VBA 消息框中

Question

我有一个基于下拉列表运行的宏。下拉列表中有三个选项。我为每个掉落创建了一条自定义警告消息，效果很好。我想在这段代码中添加"is"和“否”按钮选择，但我似乎无法让它正常工作。

我似乎只能做其中一个或。每个选择都有相同的警告消息，但带有"is"和“否”，或者每个选择都有自定义消息，但只有“确定”选项，没有"is"和“否”按钮选择。

Sub CopyRanges()

Dim message As String

If Sheets("Data").Range("D27") = "6-18" Then
    message = "You are about to change the size range, are you sure?"
Msgbox message
End If

If Sheets("Data").Range("D27") = "XS/S-L/XL" Then
    message = "You are about to change the size range to DUAL size, some POM's will not be available using the DUAL size range. Are you sure you wish to proceed?"
Msgbox message
End If

If Sheets("Data").Range("D27") = "XXS-XXL" Then
    message = "This size range is only for Fully Fashionesd Knitwear. Cut and sew styles please use the size 6-18 size range. Are you sure you wish to proceed?"
Msgbox message
End If

Answer 1

您可以向 Msgbox 添加选项(提供完整列表 here )。

通过上面提供的链接，Msgbox 的完整语法为:

MsgBox (prompt, [ buttons, ] [ title, ] [ helpfile, context ])

<小时/>

您想要访问按钮选项。实际上，它看起来像这样:

Dim Ans 'Answer
Ans = Msgbox (message, vbYesNo)

If Ans = vbYes Then
    'Do what if yes
Else
    'Do what if no
End If

<小时/>

此外，Select Case 在这里效果很好

Sub CopyRanges()

Dim message1 As String: message1 = "You are about to change the size range, are you sure?"
Dim message2 As String: message2 = "You are about to change the size range to DUAL size, some POM's will not be available using the DUAL size range. Are you sure you wish to proceed?"
Dim message3 As String: message3 = "This size range is only for Fully Fashionesd Knitwear. Cut and sew styles please use the size 6-18 size range. Are you sure you wish to proceed?"
Dim Ans as VbMsgBoxResult

Select Case Sheets("Data").Range("D27")
    Case "6-18"
        Ans = MsgBox(message1, vbYesNo)
            If Ans = vbYes Then
                'What if yes?
            Else
                'What if no?
            End If

    Case "XS/S-L/XL"
        Ans = MsgBox(message2, vbYesNo)
            If Ans = vbYes Then
                'What if yes?
            Else
                'What if no?
            End If

    Case "XXS-XXL"
        Ans = MsgBox(message3, vbYesNo)
            If Ans = vbYes Then
                'What if yes?
            Else
                'What if no?
            End If

End Select

End Sub

最后，如果您的 3 个 yes 语句导致完成 3 个本质上不同的任务，您可以考虑创建 3 个处理不同任务的子任务。然后，您可以简单地在每种情况下调用适当的子程序。它将保持代码干净，我总是鼓励分离过程以允许专门的宏，而不是一劳永逸方法