Read our blogs, tips and tutorials
Try our exercises or test your skills
Watch our tutorial videos or shorts
Take a self-paced course
Read our recent newsletters
License our courseware
Book expert consultancy
Buy our publications
Get help in using our site
546 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owl trainers only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
Search our website
We also send out useful tips in a monthly email newsletter ...
Giving the user a choice of buttons to click isn't much use unless you capture their response. This page describes how to capture which button the user clicked on a message box.
What's the point of asking important questions like this if you can't see the answer?
You don't need any files for this section.
Click here to download the sample code used on this page.
You can declare a variable with the VbMsgBoxResult type to store the result of a message box. In a new workbook, insert a module in the VBE and create a subroutine called CaptureButtonClicked. Add a variable declaration to the subroutine as shown below:
You should see VbMsgBoxResult appear in the IntelliSense list when you declare the variable.
You could also use a simple numeric data type such as Byte or Integer to store the result of a message box, but you'll find VbMsgBoxResult more useful when it comes to testing the result.
You can assign the result of a message box to the variable you've declared, as shown in the code below:
When you want to capture the result of a message box, it's important to enclose its arguments in parentheses (round brackets).
Running the code shown above will display a message box as normal:
Hopefully you'll be clicking Yes at this point!
If you step through the code using the F8 key and use the Locals window (choose View | Locals Window from the menu if you can't see it), you can see which button was clicked:
You can see the name of the constant stored in the variable.
Hovering the mouse cursor over the variable while you're stepping through the code will reveal the underlying value of the constant stored in the variable:
After you have clicked a button on the message box, hover the mouse cursor over the variable name to show its value.
To practise storing the result of a message box:
Sub ASimpleChoice()
MsgBox "Click any button", vbYesNoCancel
End Sub
Sub ASimpleChoice()
Dim UserChoice As VbMsgBoxResult
MsgBox "Click any button", vbYesNoCancel
End Sub
Sub ASimpleChoice()
Dim UserChoice As VbMsgBoxResult
UserChoice = MsgBox("Click any button", vbYesNoCancel)
End Sub
Don't forget to put parentheses around the argument list!
Sub ASimpleChoice()
Dim UserChoice As VbMsgBoxResult
UserChoice = MsgBox("Click any button", vbYesNoCancel)
MsgBox "The value of your choice is " & UserChoice
End Sub
This is what you should see if you click Yes. 6 is the underlying value of the vbYes constant.
Kingsmoor House
Railway Street
GLOSSOP
SK13 2AA
Landmark Offices
99 Bishopsgate
LONDON
EC2M 3XD
Holiday Inn
25 Aytoun Street
MANCHESTER
M1 3AE
© Wise Owl Business Solutions Ltd 2024. All Rights Reserved.