Program by Levels

Tutorial on VBA for Excel (Macros)

bringing you the simplest and most efficient ways
to do things in VBA for Excel

VBA Lesson 2-7: Message Boxes (MsgBox) and Input Boxes in Excel
(Levels: Intermediate)

In alphabetical order here are the 13 VBA words that you need to learn to work efficiently with message boxes and input boxes:

InputBox, MsgBox, vbCritical, vbExclamation, vbInformation, vbNo, vbOkCancel, vbOkOnly, vbQuestion, vbRetryCancel, vbYes, vbYesNo, vbYesNoCancel

VBA for Excel Code for Message  Boxes

In VBA for Excel the message box (MsgBox) is the primary tool to interact with the user. You can use it to inform, alert him or ask him to choose a certain path (Yes/No).

The code in VBA for Excel to generate the following basic message box is:
MsgBox "Your message here"

When you develop a procedure that takes time to run, you end your code with a basic message box telling the user that the procedure has been executed.
MsgBox "The report is ready"
or
MsgBox "The procedure has been executed you may now go to sheet "" Cover" ""
or
MsgBox "The procedure has been executed you may now go to cell ""A1"""

Notice that if you want part of your message to be between quotation marks you have to double the quotation marks within the message.

When you develop a VBA procedure that will do something important like delete all data always start the code with a message box that asks the user if he is certain that he wants the procedure to run. In this case you will use a "Yes/No" message box.

When working with a "Yes/No" message box you will need to collect the answer given by the user.

Step 1: You first declare a variable to store the answer "Dim varAnswer as String" .

Step 2: You store the answer in the variable with the code: "varAnswer = MsgBox("Are you sure that you want to delete all data?" , vbYesNo, "Warning")" . Notice the parentheses within which you submit the three arguments: the message, the type of message box and its title. Notice also the quotation marks surrounding the message and the title.

Step 3: You deal with the answer with an "IF" statement where if the user answers "No" you just exit the procedure with "Exit Sub"

Here is the whole procedure.

Sub proDangerousProcedure()

Dim varAnswer As String
        varAnswer = MsgBox("Are you sure that you want to delete all data?" , vbYesNo, "Warning")
        If varAnswer = vbNo Then
                Exit Sub
        End if
        Here the dangerous procedure starts
End Sub

When you create a basic message box (first example above), you don't need to use parentheses. If you develop a more complex message box you will see that when you start writing the line of code (example above) varAnswer = MsgBox( right after the parenthesis Excel shows you what it is expecting as arguments:

1- The prompt is not optional, it is the message that you want your user to read in the message box and it must be between quotation marks.
2- When you enter the comma after the prompt Excel offers you a drop-down list of all the types of message box that you can create.

Choose your message box and enter a comma.
3- The third argument is the title of the message box that will appear in the blue band at the top of the message box. Submit a title between quotation marks.

The fourth and fifth arguments are rarely used, they are optional and you don't need to enter anything else...but the closing parenthesis.

As you can see there are many types of message boxes.  The ones that are most often used are the vbYesNo, vbOKCancel, vbRetryCancel. You might also want to use a fancier message box like the vbQuestion, the vbInformation, the vbExclamation or the vbCritical as shown below.

This page is one of the 33 lessons from the
Tutorial on VBA for Excel (Macros)
VBA for Excel made simple