spot_img

VBA MsgBox

The MsgBox is a dialog box in the excel VBA that can be used to inform the users of your program.

It displays a pop-up style message box and waits for the user to click a button, and then an action is performed based on the clicked button by the user.

It provides a way for the end-users to interact with a workbook. It can be a simple alert to the users, or it can be complicated that needs an action to proceed by the users.

Syntax

Prompt: (required parameter) it refers to a text that is displayed as a message in the dialog box. The maximum length of the posted message can be 1024 approximately. If the word crosses the defined range, then the message will be divided using the carriage return character (Chr(13)) or a linefeed character (Chr(10)) between each line.

Buttons: (optional parameter) it denotes a numeric expression to display the types of buttons, to use the icon style, the identity of the default button, and the modality of the message box. The default value for buttons is zero if the left side of the button is blank.

Title: (optional parameter) the title bar of the dialog box displayed the string expression. If the left side of the dialog box is blank, then the application name is placed in the title bar.

Helpfile: A string parameter that identifies the help file to use for providing context-sensitive help for the dialog box.

Context: The help author assigns the Help context number to an appropriate topic. If the context is provided, then the help file must also be provided.

We can configure the message box in many ways with the help of the button parameters. That shows in the below table, such as:

Constant Value Description
vbOKOnly 0 Display OK button only
vbOKCancel 1 Display OK and Cancel buttons
vbAbortRetryIgnore 2 Display Abort, Retry and Ignore buttons
vbYesNoCancel 3 Display Yes, No, and Cancel buttons
vbYesNo 4 Display Yes and No buttons
vbRetryCancel 5 Display Retry and Cancel buttons
vbCritical 16 Display Critical Message icon
vbQuestion 32 Display Warning Query icon
vbExclamation 48 Display Warning Message icon
VbInformation 64 Display Information Message icon
vbDefaultButton1 0 The first button is default
vbDefaultButton2 256 The second button is default
vbDefaultButton3 512 The third button is default
vbDefaultButton4 768 The fourth button is default
vbApplicationModal 0 The user must respond to the message box
vbSystemModal 4096 All applications are suspended until the user responds to the message box
vbMsgBoxHelpButton 16384 Adds Help button to the message box
vbMsgBoxSetForeground 65536 Specifies the message box window as the foreground window
vbMsgBoxRight 524288 Text is eight-aligned
vbMsgBoxRtlReading 1048576 Specifies text should appear as right-to-left reading on Arabic and Hebrew systems

The above values are divided into four groups as the first group of values (0-5) describes the number and type of buttons displayed in the dialog box. The second group of values (16, 32, 48, and 64) illustrates the icon style. The third group of values (0, 256, and 512) determines which button is the default. And the modality of the message box is defined in the fourth group (0, 4096). We can add only one number from each group to create a final value for the buttons argument.

Return Values

The MsgBox function returns any one value from the following values, which is used to identify the button. And the only thing that the user has to do that clicked in the message box.

Constant Value Description
vbOK 1 OK
vbCancel 2 Cancel
vbAbort 3 Abort
vbRetry 4 Retry
vbIgnore 5 Ignore
vbYes 6 Yes
vbNo 7 No

Examples

Suppose we want to display a message box with Yes, No, and Cancel buttons, as shown in the below code:

VBA MsgBox

Executes the above function by clicking on the run button on the VBA window. It displays a “Welcome” message box within a message box and an “OK” button.

VBA MsgBox

After clicking the OK button, another dialog box is displayed with a message “do you like the red color” and “yes”, “no”, and “cancel” buttons.

VBA MsgBox

After clicking any button (e.g., yes), the value of that button is stored as an integer. And it displayed a pop-up message box to the user, as shown below. Using this value, we can understand which button was clicked by the user.

VBA MsgBox


Next TopicVBA Comment

spot_img
Previous articleVBA Data Types
Next articleCircular reference in Excel