EXCEL VIDEO TUTORIALS / EXCEL DASHBOARD REPORTS

 Excel Training VBA Lesson 8

Excel VBA Message Box (MsgBox) Function

<< PREVIOUS LESSON |NEXT LESSON>> | BACK TO EXCEL VBA LEVEL1 TRAINING INDEX

MESSAGE BOX FUNCTION

Excel has available some useful functions that allow us to either inform the user and/or collect information from the user. The most common of these is the Message box function. While this function is very informative it is also very easy to use. For instance, to display a simple message to a user you would only need to use this:

Sub MessageBox

MsgBox "Hello, my name is David."

End sub

This is using the message box in it's simplest form. Notice that to tell Excel we want a message box we use the abbreviation MsgBox.  If we had other code after our MsgBox function, our procedure would pause until the user has acknowledged the message.

The syntax for the MsgBox function is:

MsgBox(prompt[, buttons] [, title] [, helpfile, context])

The only part of the MsgBox function that is required is the Prompt, all other arguments are optional.

There is a lot of good detailed information on the MsgBox function within the Excel VBA help. Just type MsgBox in any module, select the word then push F1. I strongly suggest reading up on this function as it is very useful.

I will use this lesson to show you how you can determine which button they have clicked on a MsgBox should it have more than one button. Generally when you wish to 'capture' the return value of a function we need to enclose it within parenthesis. The MsgBox is no exception.

Let's imagine we wish to ask the user if they would like to save their file after a procedure has run. To do this we could use: 

Sub WhichButton()
Dim iReply As Integer
'<any code>

    iReply = MsgBox("Would you like to save now?", _
             vbYesNo, "OzGrid Example")
                If iReply = vbYes Then ThisWorkbook.Save
End Sub

This is how you could return to VBA, the button clicked by the user (Yes or No). Notice how we have used a variable dimmed as a Integer. This is simply because the MsgBox function will return a whole numeric value (Integer) for the button clicked. Each of these Integers that are returned also have a Constant. In our example above, if the user clicks "Yes", the Integer returned is 6 and it's Constant equivalent is "VbYes". Should the user select "No" the value returned would be 7 and the Constant "VbNo". It really is that simple!

The values and Constant returned are show in the table below taken from the Excel help:

Sub WhichButtonDefault()
Dim iReply As Byte, iType As Integer
'<any code>

    ' Define buttons argument.
      iType = vbYesNo + vbCritical + vbDefaultButton2

        iReply = MsgBox("Would you like to save now?", _
                        iType, "OzGrid Example")
            If iReply = vbYes Then ThisWorkbook.Save
End Sub

In the example above we have told Excel that we wish to make the "No" button our default. This was done with the use of: vbDefaultButton2. We also told Excel to make our message box Critical and this was done with: vbCritical. There are many optional arguments for the optional "buttons" argument and are shown in the table below taken from the Excel help.

<< PREVIOUS LESSON |NEXT LESSON>> | BACK TO EXCEL VBA LEVEL1 TRAINING INDEX