EXCEL VIDEO TUTORIALS / EXCEL DASHBOARD REPORTS

Ozgrid, Experts in Microsoft Excel Spreadsheets

 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:


ConstantValueDescription
vbOK1OK
vbCancel2Cancel
vbAbort3Abort
vbRetry4Retry
vbIgnore5Ignore
vbYes6Yes
vbNo7No


You may have noticed when you ran the example code, in the WhichButton procedure, that the default button when the MsgBox was shown was "Yes" (first button). If we want to change this we need to assign our "button" argument to a variable. We can also use this variable to store our message box type and make our message of the Critical type, or one of many other types.

 


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.

ConstantValueDescription
vbOKOnly0Display OK button only.
vbOKCancel1Display OK and Cancel buttons.
vbAbortRetryIgnore2Display Abort, Retry, and Ignore buttons.
vbYesNoCancel3Display Yes, No, and Cancel buttons.
vbYesNo4Display Yes and No buttons.
vbRetryCancel5Display Retry and Cancel buttons.
vbCritical16Display Critical Message icon.
vbQuestion32Display Warning Query icon.
vbExclamation48Display Warning Message icon.
vbInformation64Display Information Message icon.
vbDefaultButton10First button is default.
vbDefaultButton2256Second button is default.
vbDefaultButton3512Third button is default.
vbDefaultButton4768Fourth button is default.
vbApplicationModal0Application modal; the user must respond to the message box before continuing work in the current application.
vbSystemModal4096System modal; all applications are suspended until the user responds to the message box.
vbMsgBoxHelpButton16384Adds Help button to the message box
VbMsgBoxSetForeground65536Specifies the message box window as the foreground window
vbMsgBoxRight524288Text is right aligned
vbMsgBoxRtlReading1048576Specifies text should appear as right-to-left reading on Hebrew and Arabic systems

 

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