Microsoft Office Tutorials and References
In Depth Information
Chapter 4: Controlling Forms with VBA
Values That MsgBox Passes to the Variable
Responding to what the user clicks
By using decision-making code, you can have your VBA procedure do
something when someone clicks a button on your message box, based on the
contents of the myVar variable. The sample message box displays a Yes button
and a No button. If the user clicks the Yes button, myVar contains vbYes (or
6). If the user clicks the No button, myVar contains vbNo (or 7). The skeletal
structure of the code that decides what to do — based on the button clicked
(where Do these statements can be any number of VBA statements) —
is shown in Listing 4-1.
Listing 4-1: Message-Box Response Code in Which a Constant Refers
‘Show a message box with Yes and No buttons.
Dim myVar as Byte
myVar = MsgBox(“Are you sure?”,vbYesNo+vbQuestion)
‘Decide what to do next based on button clicked in box.
If myVar = vbYes Then
‘ Do these statements if Yes
‘ Do these statements if No
You can use either the constant or the numeric value to refer to the contents
of the myVar variable. The code in Listing 4-2 works exactly the same way as
the code in Listing 4-1.