MESSAGE BOXES

A simplified representation of the syntax of a message box is:

MsgBox promt[, buttons] [, title]

where prompt is a String expression displayed as the message in the dialog box, buttons is an optional argument that sets up the buttons on the message box (if you omit it, the default is the OK button), title is an optional String expression displayed in the title bar of the dialog box (if you omit it, the default is the application name).

The simplest application is to display a message with no frills:

MsgBox "Click OK to continue"

When the user click OK, the program proceeds to the statement following the MsgBox statement.

If you display a very long message, VBA will automatically wrap the text around about 128 characters.

You can insert a carriage return character, Chr(13), whenever you want to break a line.

Messages can also be enhanced by adding supplementary information to the message and by adding a title:

velocity = 24.5 Msg = "The velocity is " & velocity & "m/s" Title = "Parachutist Problem" MsgBox Msg, , Title Because we did not specify a button, the argument is omitted (as signified by the empty space between the two commas).

Buttons in Message Boxes

Other than displaying the default OK button, VBA has many other types of buttons having different style and operations. These different choices are specified by supplying an argument with the button argument of the MsgBox function. VBA has built-in constants for use with these button arguments:

   Category                   Description                         String value     Numeric value

Number and type     Display OK button only.                     vbOKOnly                0
                    Display OK and Cancel buttons.              vbOKCancel              1
                    Display Abort, Retry, and Ignore buttons.   vbAbortRetryIgnore      2
                    Display Yes, No, and Cancel buttons.        vbYesNoCancel           3
                    Display Yes and No buttons.                 vbYesNo                 4
                    Display Retry and Cancel buttons.           vbRetryCancel           5

Icon style          Display Critical Message icon.              vbCritical              16
                    Display Warning Query icon.                 vbQuestion              32
                    Display Warning Message icon.               vbExclamation           48
                    Display Information Message icon.           vbInformation           64

Default buttons     First button is default.                    vbDefaultButton1         0
                    Second button is default.                   vbDefaultButton2       256
                    Third button is default.                    vbDefaultButton3       512
                    Fourth button is default.                   vbDefaultButton4       768
The first group of values (0-5) describes the number and type of buttons displayed in the dialog box, the second group (16, 32, 48, 64) describes the icon style, and the third group (0,256, 512, 768) determines which button is the default -- this is, which will be highlighted so that it will be automatically chosen if you click none of the buttons and just hit the Enter key.

Their corresponding numerical values are to be added up to obtain a final value for the button argument. However no more than one value is supposed to be taken from each group. One should use the constant String values rather than their explicit numerical values.

As an example,to obtain a message box with Yes, No, and Cancel buttons and the Warning Query icon, we write:

MsgBox "Do it again?", vbYesNoCancel + vbQuestion

The value for the button argument is given by3 + 32 = 35

When a button is clicked, the next line in the program is executed. Depending on the type of button is clicked, a unique value is returned. The following table lists the constants that are returned from button selections.
String      Numeric value   Description
vbOK             1             OK
vbCancel         2             Cancel
vbAbort          3             Abort
vbRetry          4             Retry
vbIgnore         5             Ignore
vbYes            6             Yes
vbNo             7             No
This information can be used to trigger some action.

To obtain a return value from the button clicked by the user, we need to use the following syntax for a message box:

v = MsgBox(prompt[, buttons] [, title])

where v is a variable to which a constant value representing the choice of button is assigned. The message box acts like a function in that it returns a value. Hence the parentheses must be included.

Here is an example:

msg = "Do you want to marry me?"
style = vbYesNo + vbQuestion + vbDefaultButton1
title = "MsgBox Demo"
response = MsgBox(msg, style, title)
If response = vbYes Then
    myMsg = "This is unbelievable!"
Else
    myMsg = "Oh no! I am going to kill myself!"
End If
MsgBox myMsg