Excel Visual Basic for Applications online training

This page has 0 threads | Add post

Finding our tutorials useful? Please consider helping us to share more!

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.

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:

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:

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!

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:

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:

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

Sub ASimpleChoice()

MsgBox "Click any button", vbYesNoCancel

End Sub

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

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!

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

Run the subroutine and make your choice:

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

Save and close the workbook.

This page has 0 threads

Add a new post

MODULES

LESSONS

TOPICS

Files Needed

Completed Code

Declaring a Variable to Hold the Result of a Message Box

Assigning the Result of a Message Box to a Variable

Debugging with Message Box Results

Head office

London

Manchester