Excel Visual Basic for Applications online training

This page has 0 threads | Add post

7,359 reviews

Self-paced training

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.1 - Displaying Different Buttons

Displaying different buttons on a message box allows you to ask users important questions:

Many users in the UK will be curious as to why there is a No option on this message.

Files Needed

You don't need any files for this section.

Completed Code

You can click here to download a workbook containing the completed code.

Create a new workbook and insert a new module in the VBE.

Displaying Different Buttons

By default, a message box will display only an OK button. You can choose from several other combinations of buttons using the Buttons parameter. Create a new subroutine called ChoosingDifferentButtons and add code to display a message box which displays different buttons:

After entering a prompt for the message, type a comma to see a list of button options. Choose the vbYesNo option.

If the list shown above disappears, you can press CTRL + SHIFT + J to display it.

Running the code will display the message box shown below. It doesn't matter which button you click as we haven't captured the result yet!

Yes is, of course, the correct response. Unfortunately, our code has no way to detect our response yet.

Naming Arguments

You can use named arguments to make the code easier to read. Add a second message box to the same subroutine using the code shown below:

Here we've written the parameter name in front of each argument. We've also used continuation characters (a space and underscore) to write the code on multiple lines.

The above code produces a message box resembling the one shown below:

Users from the US should note that a biscuit is equivalent to a cookie.

The Available Buttons

You can see the available button combinations in the table below:

Constant	Buttons
vbAbortRetryIgnore
vbOKCancel
vbOKOnly
vbRetryCancel
vbYesNo
vbYesNoCancel

Displaying Buttons and an Icon

You can combine a choice of buttons with a choice of icon on the same message box:

You could replace Brexit with Trump or North Korea for a more international flavour.

To do this, create a subroutine called ButtonsAndIcons. Add code to display a message box as shown below:

You can add together a choice of buttons and icon using the + symbol.

Running the above code results in a message box which resembles the one shown below:

You can specify one set of buttons and one icon per message box.

Including a Title

You can include a title on the message box as well as a prompt, buttons and an icon. Add a second message box to the same subroutine using the code shown below:

Named arguments make it easier to see what's going on.

Running the code shown above produces a message box with a customised title, icon and buttons:

Not that anyone will read the title.

To practise choosing which buttons to display on a message box:

Using the same module, create a subroutine called AskYourselfOneQuestion.
Add code to the subroutine to display a simple message box (your question doesn't need to be as aggressive as the one shown below):

Sub AskYourselfOneQuestion()

MsgBox _

"Do I feel lucky?" & vbNewLine & _

"Well, do you, punk?"

End Sub

Run the subroutine to check that the message appears, then click OK to return to the code.
Edit the code so that your message box displays a choice of buttons to click, as well as a different title:

Sub AskYourselfOneQuestion()

MsgBox _

"Do I feel lucky?" & vbNewLine & _

"Well, do you, punk?", _

vbYesNo, _

"You've got to ask yourself one question:"

End Sub

Run the subroutine again to check that you have multiple buttons on the message box:

Choose your answer carefully...

Save and close the workbook.

To practise displaying buttons and an icon on a message box:

Using the same module, add a new subroutine called KnowMore.
Add code which displays a simple message box:

Sub KnowMore()

MsgBox _

"Would you like to know more?"

End Sub

Extend the code so that the message box displays an icon and different buttons:

Sub KnowMore()

MsgBox _

"Would you like to know more?", _

vbQuestion + vbYesNo

End Sub

Run the subroutine to check that the icon and buttons appear as you expect:

You should probably click Yes if you'd like to continue with the course.

Save and close the workbook.

This page has 0 threads

Add a new post

MODULES

LESSONS

TOPICS

Files Needed

Completed Code

Displaying Different Buttons

Naming Arguments

The Available Buttons

Displaying Buttons and an Icon

Including a Title

Head office

London

Manchester