Getting a response from a message box

Your VBA code can also determine which button was clicked in a message box. You can assign the result of the MsgBox function to a variable. In the following code, I use some built-in constants (which I describe later in Table 15-2) that make it easy to work with the values returned by MsgBox:

Sub

GetAnswer()

Dim Ans As Integer

Ans = MsgBox("Continue?", vbYesNo)

Select Case Ans

Case vbYes

...[code if Ans is Yes]...

Case vbNo

...[code if Ans is No]...

End Select

End

Sub

When you execute this procedure, the Ans variable is assigned a value of either vbYes or vbNo, depending on which button the user clicks. The Select Case statement uses the Ans value to determine which action the routine should perform.

You can also use the MsgBox function result without using a variable, as the following example demonstrates:

Sub GetAnswer2()

If MsgBox("Continue?",

vbYesNo) = vbYes Then

' ...[code if Yes is

clicked]...

Else

' ...[code if Yes is

not clicked]...

End If

End Sub

0 0

Post a comment