What are Message Boxes?
Whether you're writing Visual Basic for Applications code in Excel, Access, PowerPoint, Word or any other application, you can use the MsgBox command to display a pop-up message on screen.
Don't confuse message boxes with input boxes, the subject of a separate tutorial. Message boxes display a message on screen; input boxes ask you to type something in.
Message boxes thus displayed can be simple:
A simple message box, without any customisation at all.
Or customised:
In this message box, we've changed the titles and buttons and added a question mark symbol.
If you want to customise your message boxes more than this, you're out of luck - you can only add so many bells and whistles. In such a case you're best off creating a user form, which you can format as much as you like.
The Parts of a Message Box
The component parts of a message box are shown below:
A typical (?) message box in VBA.
The parts of a message box shown above are as follows:
Number | Part | What it shows |
|---|
1 | Title | The caption of the dialog box |
2 | Prompt | The message which appears |
Syntax of a Message Box
The easiest way to see how to create a message box is from the Intellisense offered:
The list of arguments to the MsgBox command
The peculiar argument is Buttons, about which more later in this tutorial. You can display a message box either by listing the arguments in the right order, or by using named arguments - an example of each follows.
Displaying a Message Box using Positional Arguments
The easiest way to display a message box is by entering the arguments in the right order:
MsgBox "Cats like plain crisps", vbOKOnly, "Shock claim"
In this case, the arguments are assumed to be in the right order, so we have:
Argument | Name | What it means | Value here |
|---|
1 | Prompt | The message to be displayed | Cats like plain crisps |
2 | Buttons | The buttons, etc, to display | vbOkOnly |
3 | Title | The title for the mesage box | Shock claim |
Running this macro line would give:
The message box has all of the things we wanted: a prompt, an OK button and a title.
If you want to omit an argument using this method, you must include an extra comma. For example, the following message would not set any value for the Buttons argument:
MsgBox "Hello, World", , "Greeting"
Displaying a Message Box using Named Arguments
if you have the energy, it's better practice to use named arguments:
MsgBox _
prompt:="Cats like plain crisps", _
Buttons:=vbOKOnly, _
Title:="Shock claim"
This has the advantage of being easier to read - and you can put the arguments in any order.
Often you will want to create a message to be displayed on screen by joining bits of text together. You can do this using the & symbol (which is pronounced Ampersand).
Example One - a Greeting System
The following example uses two simple input boxes (covered in a separate tutorial) to ask a user what their name and preference is, then redisplays the choices made in a message box:
Sub ShowName()
Dim YourName As String
Dim YourBag As String
YourName = InputBox("What is your name?", "Choose name")
YourBag = InputBox("What bag are you into?", "Choose bag")
MsgBox "Hello, " & YourName & ". Good to hear you're into " & YourBag & "."
End Sub
Here's what you see if you choose Bob and Sheep in reply to the two questions:
Every time you join two bits of text together, you need an ampersand (&). Here we have no less than 5 bits of text we're joining together: the word Hello, the name of the person, the middle bit of text, the person's preference and a trailing full stop - so we need 4 ampersands.
Example Two - Displaying Worksheet Names
The following code displays a list of all of the worksheet names in the active workbook.
Note the use of VbCrLf - standing for Visual basic Carriage return Line feed - to insert a line break. Older programmers might like to know that this represents the ASCII characters 13 and 10 joined together!
Sub ShowSheetNames()
Dim i As Integer
Dim msg As String
msg = "This file has the following worksheets:" & vbCrLf
For i = 1 To Sheets.Count
msg = msg & vbCrLf & Sheets(i).Name
Next i
MsgBox msg
End Sub
You can also use vbNewLine to throw a new line, which is easier to type and remember.
Here's what you might get if you ran this macro:
Notice that each worksheet name is on a different line of the message.
Displaying Different Symbols
Now that we've learnt how to display plain messages, let's see if we can tart them up a bit! You can add a few bells and whistles to a message box - but only a few.
You can display 4 different symbols:
Key word | Symbol |
|---|
vbCritical |  |
vbQuestion |  |
vbExclamation |  |
vbInformation |  |
So, for example, this would display a message box with an exclamation mark symbol and an OK button (bizarrely, you add your choices together):
MsgBox "Cats like plain crisps", vbExclamation + vbOKOnly
Displaying Different Buttons
In addition to the symbols above, you can control which buttons you see as follows:
Key word | Buttons that you get |
|---|
vbOkCancel | OK and Cancel |
vbOkOnly | OK only |
vbYesNo | Yes and No |
vbAbortRetryIgnore | Abort Retry and Ignore |
vbRetryCancel | Retry and Cancel |
vbYesNoCancel | Yes No and Cancel |
To understand why you would display different combinations of buttons, see the following part of this tutorial on askign questions using message boxes.
Asking Questions with Message Boxes
Everything in this tutorial so far has concentrated on displaying a message on screen (we haven't been interested in the button the user chooses). But how about this?
This message box allows you to choose YES or NO - the code then detects the button chosen and acts accordingly.
In this case we use MsgBox as a function, not a command. So instead of:
Here MsgBox is used as a command
We have instead:
Here MsgBox is used as a function
Confused? Us too. Even though it's the same word MsgBox, the syntax is very different. The rule is that if you use brackets, you must store the return value in a variable.
The Code to Use MsgBox as a Function
Here is a typical routine to get a message box to appear as a question:
Sub SaveCurrentWorkbook()
Dim ButtonChosen As Integer
ButtonChosen = MsgBox("Do you want to continue?", vbQuestion + vbYesNo + vbDefaultButton2, "Continue?")
If ButtonChosen = vbYes Then
ActiveWorkbook.Save
Else
Exit Sub
End If
End Sub
The combination of symbols and buttons we choose is as follows:
Choice | Why |
|---|
vbQuestion | To get a nice question mark symbol appearing! |
vbYesNo | To get two buttons to appear: Yes and No |
vbDefaultButton2 | To make sure that if a user chooses Enter, they will by default choose No, not Yes |
And that is the full story on the MsgBox command-cum-function!
