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.3 - Testing the Result

Once you've captured the user's response you'll need to test which button they clicked in order to work out what to do next.

It's vital to be able to test which button the user clicks.

Files Needed

Click here to download the file needed for this part of the lesson.

Completed Code

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

Extract and open the workbook linked to in the Files Needed section above. In the VBE, find the subroutine called TestWhichButtonWasClicked in Module1.

Testing the Result of a Message Box

After you've captured which button was clicked, all you need to do is test which one it is. Add an If statement at the bottom of the TestWhichButtonWasClicked subroutine, as shown in the diagram below:

If you've used the VbMsgBoxResult data type for your variable, you should receive some help when it comes to testing the result.

You can explicitly test for as many or as few buttons as you need. Complete the If statement according to the example shown below:

This simple example tests whether the user clicked Yes or No and displays an appropriate message in each case.

Run the subroutine a couple of times to check that you see the correct result depending on which button you click.

Using the Value of a Constant

VbMsgBoxResult is an example of an enumeration: a collection of related constants. Each constant in this enumeration refers to one of the possible buttons that can be clicked on a message box. Each constant has both a name and an underlying numeric value, which you can see in the table below:

Constant	Value
vbOK	1
vbCancel	2
vbAbort	3
vbRetry	4
vbIgnore	5
vbYes	6
vbNo	7

This means that you could also test for the result of a message box using a number.

This code works but is less easy to read than the previous example.

To practise testing the result of a message box:

Using the same module, add a new subroutine called ControversialQuestion.
Add code to ask the user a difficult question and capture their response in a variable:

Sub ControversialQuestion()

Dim Answer As VbMsgBoxResult

Answer = MsgBox( _

"Is it ever OK to put pineapple on pizza?", _

vbYesNo + vbQuestion, _

"Moral Quandary")

End Sub

Run the subroutine to check that the message appears as you expect:

You could ask a less controversial question, such as:

Add code to test the result of the message box and display an appropriate response:

Sub ControversialQuestion()

Dim Answer As VbMsgBoxResult

Answer = MsgBox( _

"Is it ever OK to put pineapple on pizza?", _

vbYesNo + vbQuestion, _

"Moral Quandary")

If Answer = vbYes Then

MsgBox _

"Wrong - it is an affront to nature!", _

vbCritical

ElseIf Answer = vbNo Then

MsgBox _

"Correct - they should only be used for pina coladas"

End If

End Sub

Run the subroutine and test that you see the appropriate response after choosing an answer:

This is the message you should see if you click Yes. I should confess that I actually like pineapple on pizza.

Edit the message box code so that it also displays a cancel button (use vbYesNoCancel to do this).
Add an Else clause to the If statement which displays an appropriate message if the user clicks the cancel button:

Sub ControversialQuestion()

Dim Answer As VbMsgBoxResult

Answer = MsgBox( _

"Is it ever OK to put pineapple on pizza?", _

vbYesNoCancel + vbQuestion, _

"Moral Quandary")

If Answer = vbYes Then

MsgBox _

"Wrong - it is an affront to nature!", _

vbCritical

ElseIf Answer = vbNo Then

MsgBox _

"Correct - they should only be used for pina coladas"

Else

MsgBox _

"You should really pick a side!", _

vbExclamation, _

"Taking the coward's way out"

End If

End Sub

Run the subroutine and check that you see the appropriate response for each of the choices.

This page has 0 threads

Add a new post

MODULES

LESSONS

TOPICS

Files Needed

Completed Code

Testing the Result of a Message Box

Using the Value of a Constant

Head office

London

Manchester