Module 5 - Messages and User Inputs
Lesson 5.2 - Capturing the User Response
Topic 5.2.2 - Capturing the Result

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.

Simple question

What's the point of asking important questions like this if you can't see the answer?

 

Files Needed

You don't need any files for this section.

Completed Code

Click here to download the sample code used on this page.

Declaring a Variable to Hold the Result of a Message Box

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:

Declaring variable

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.

Assigning the Result of a Message Box to a Variable

You can assign the result of a message box to the variable you've declared, as shown in the code below:

Storing result of message box

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:

Message box

Hopefully you'll be clicking Yes at this point!

 

Debugging with Message Box Results

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:

Locals window

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:

Hover mouse

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:

  1. In the same module, create a subroutine called ASimpleChoice.
  2. Write code to display a message box which displays multiple buttons:

Sub ASimpleChoice()

 

MsgBox "Click any button", vbYesNoCancel

 

End Sub

  1. Declare a variable to hold the result of a message box (you can use a different variable name if you like):

Sub ASimpleChoice()

 

Dim UserChoice As VbMsgBoxResult

 

MsgBox "Click any button", vbYesNoCancel

 

End Sub

  1. Edit the code to return the result of the message box to the variable you have declared:

Sub ASimpleChoice()

 

Dim UserChoice As VbMsgBoxResult

 

UserChoice = MsgBox("Click any button", vbYesNoCancel)

 

End Sub

Don't forget to put parentheses around the argument list!

  1. Display a final message to the user which shows the underlying value of the button they have clicked:

Sub ASimpleChoice()

 

Dim UserChoice As VbMsgBoxResult

 

UserChoice = MsgBox("Click any button", vbYesNoCancel)

 

MsgBox "The value of your choice is " & UserChoice

 

End Sub

  1. Run the subroutine and make your choice:
Final message

This is what you should see if you click Yes6 is the underlying value of the vbYes constant.

 
  1. Save and close the workbook.
This page has 0 threads Add post