MsgBox Function in VBA

Buzz

Content

Structure of MsgBox in VBA.

Syntax of MsgBox Function in VBA.

Constants for MsgBox Buttons in VBA.

In the previous articles, you explored TRIM and DIR functions in VBA with Mytour. In this article, Mytour continues the journey by introducing you to the MsgBox function in VBA.

In VBA, we can leverage the MsgBox function to display alert dialogs as shown below:

The MsgBox function is specifically used to show custom alert dialogs to users with various customizable messages or to fetch basic input messages (such as Yes/No/OK/Cancel).

When the MsgBox dialog is displayed, VBA code temporarily pauses. We need to click any button in the MsgBox to proceed with the remaining VBA code.

Continue exploring the article below by Mytour to learn about the MsgBox function in VBA, its syntax, and how to use it.

Structure of MsgBox in VBA.

A MsgBox consists of the following components:

1. Title: Used to display the message content in the alert box. If not specified, it will default to the application name, which in this case is Excel.

2. Prompt (message): This is the message we want to display. We can use it to add a few lines or even display tables/data here.

3. Button: The default is the OK button, but we can customize it to show other buttons like Yes/No, Yes/No/Cancel, Retry/Ignore, etc.

4. Close Icon: To close the alert box, we click the close icon.

Syntax of MsgBox Function in VBA.

The MsgBox function follows a syntax similar to other VBA functions:

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

Where:

- Prompt (message): is a mandatory parameter. It displays the message we see in the MsgBox. In the example above, the prompt is 'This is a sample MsgBox'. The prompt is limited to a maximum of 1024 characters and can be used to display the values of variables. In case we want to display a multi-line prompt, we can achieve this as well.

- [buttons] (buttons): determines which buttons and icons are displayed in the MsgBox. For example, using vbOkOnly will display the OK button, while using vbOKCancel will display both OK and Cancel buttons.

- [title] (title): here we can specify what to display in the message box; it will be shown on the title bar of the MsgBox. If not specified, it will display the application name.

- [helpfile]: we can specify a help file that can be accessed when the user clicks the Help button. The Help button only appears when we use button constants. If using a Help file, we also need to specify the context parameter.

- [context]: is a numerical expression or Help context number assigned to the relevant Help topic.

Note: All parameters within square brackets are optional. Only the 'prompt' parameter is mandatory.

Constants for MsgBox Buttons in VBA.

In the following section, Mytour will introduce you to various types of buttons that can be used in VBA MsgBox.

Here is a table listing the constants for buttons that can be used in VBA MsgBox:

Note: Choosing the OK button will allow the code to continue running, while clicking Cancel will halt the code.

Button vbOKOnly (default)

If you want to display both OK and Cancel buttons, use the vbOKCancel constant.

Here is the code displaying this alert dialog:

Sub DisplayDefaultMsgBox()

MsgBox 'This is a sample box'

End Sub

Note that the text string must be enclosed in double quotation marks.

Additionally, we can use the constant vbOKOnly button even without specifying any parameters; it is considered the default.

OK & Cancel Buttons

If you want to display both OK and Cancel buttons, use the vbOKCancel constant.

Sub DisplayMsgBoxAbortRetryIgnore()

MsgBox 'What do you want to do?', vbAbortRetryIgnore

End Sub

Button vbAbortRetryIgnore

Use the constant vbAbortRetryIgnore to display the Abort, Retry, and Ignore buttons.

Here is the code displaying this alert dialog:

Sub DisplayMsgBoxAbortRetryIgnore()

MsgBox 'What do you want to do?', vbAbortRetryIgnore

End Sub

Yes and No Buttons

Use the constant vbYesNo to display the Yes and No buttons.

Sub DisplayMsgBoxYesNo()

MsgBox 'Should we stop?', vbYesNo

End Sub

Retry and Cancel Buttons

We use the constant vbRetryCancel to display the Retry and Cancel buttons.

Sub MsgBoxRetryCancel()

MsgBox 'What do you want to do next?', vbRetryCancel

End Sub

Help Button

Using vbMsgBoxHelpButton

Sub MsgBoxRetryHelp()

MsgBox 'What do you want to do next?', vbRetryCancel + vbMsgBoxHelpButton

End Sub

Note: In the above code, we combine constants for two different buttons (vbRetryCancel and vbMsgBoxHelpButton). The first part displays Retry and Cancel buttons, and the second part displays the Help button.

Setting Default Button

We can use the constant vbDefaultButton1 to set the first button as the default button. This means the button is preselected, and we can press Enter to execute that button.

Below is the code to set the second button (No button) as the default button:

Sub MsgBoxYesNoCancel()

MsgBox 'What action do you want to take?', vbYesNoCancel + vbDefaultButton2

End Sub

In most cases, the leftmost button is the default button. Additionally, we can choose other buttons to be the default button using vbDefaultButton2, vbDefaultButton3, and vbDefaultButton4.

Constants for Icons in MsgBox VBA

In addition to buttons, we can customize the icons displayed in the MsgBox.

Below is a table listing the codes for displaying corresponding icons:

Important Notification Icon

If you want to display the critical notification icon in the MsgBox, use the constant vbCritical. Additionally, you can combine this constant with others by adding a + between the codes.

For example, the code below will display the default OK button with the critical notification icon:

Sub MsgBoxCriticalIcon()
MsgBox 'This is a sample box', vbCritical
End Sub

If you want to show the critical notification icon along with Yes and No buttons, use the code below:

Sub MsgBoxCriticalIcon()
MsgBox 'This is a sample box', vbYesNo + vbCritical
End Sub

Question Mark Icon

To display the question mark icon in MsgBox, use the constant vbQuestion:

Sub MsgBoxQuestionIcon()
MsgBox 'This is a sample box', vbYesNo + vbQuestion
End Sub

Alert Icon

To display the exclamation mark icon (alert icon) in MsgBox, use the constant vbExclamation.

Sub MsgBoxExclamationIcon()
MsgBox 'This is a sample box', vbYesNo + vbExclamation
End Sub

Information Icon

To display the information icon in MsgBox, use the constant vbInformation:

Sub MsgBoxInformationIcon()
MsgBox 'This is a sample box', vbYesNo + vbInformation
End Sub

Customizing Title and Prompt in MsgBox

By using MsgBox, we can customize the title and prompt in the message box.
If you don't specify the title parameter, MsgBox will automatically use the application's title (in this case, Excel).
To specify the title you want, use the following code:

Sub MsgBoxInformationIcon()
MsgBox 'Do you want to continue?', vbYesNo + vbQuestion, 'Step 1 of 3'
End Sub

Similarly, we can customize the prompts in the message box and add line breaks.

In the code below, Mytour has added a line break using vbNewLine:

Sub MsgBoxInformationIcon()

MsgBox 'Do you want to continue?' & vbNewLine & 'Click Yes to Continue', vbYesNo + vbQuestion, 'Step 1 of 3'

End Sub

Additionally, if desired, we can use the carriage return character - Chr(13) - or linefeed character - Chr(10) - to insert a new line into the prompt in the message box.

Note: We can only add a new line to the prompt, not to the title of the message box.

Assigning Values from MsgBox to a Variable

In case of clicking any button without response.

With the MsgBox function in Excel, we can determine what actions to take when the user clicks on any specific button. Simply put, each button has an associated value.

If the Yes button is clicked, the MsgBox function will return a value (6 or the constant vbYes) that we can use in our code. Similarly, when choosing No, it will return a different value (7 or vbNo) that we can use in our code.

List of Accurate Values and Constants Returned by MsgBox Function:

Next, Mytour will introduce you to controlling VBA macro code based on the button users click.

In the following code, if the user clicks Yes, it will display the message 'You clicked Yes', and if they click No, it will show 'You clicked No'.

Sub MsgBoxInformationIcon()
Result = MsgBox('Do you want to continue?', vbYesNo + vbQuestion)
If Result = vbYes Then
MsgBox 'You clicked Yes'
Else: MsgBox 'You clicked No'
End If
End Sub

In the provided code, Mytour assigns the value of the MsgBox function to the variable Result. When clicking the Yes button, the Result variable will hold the vbYes constant (or number 6), and when clicking No, it takes vbNo (or number 7).

Then, it uses the If Then Else structure to check if the Result variable holds the value vbYes. If yes, it displays the message 'You clicked Yes,' otherwise, it shows 'You clicked No.'

We can use the same concept to execute code if the user clicks Yes and exit the sub when clicking No.

Note: When assigning the output of MsgBox to a variable, we must enclose the MsgBox function's arguments in parentheses.

For instance, in the line Result = MsgBox('Do you want to continue?', vbYesNo + vbQuestion), we can observe the arguments enclosed in parentheses.

In this article, Mytour has just introduced you to the MsgBox function in VBA. Additionally, readers can explore more articles on Mytour to learn about the DIR function in VBA. If there are any inquiries or questions, feel free to leave them in the comments section below the article.

Mytour's content is for customer care and travel encouragement only, and we are not responsible.

For errors or inappropriate content, please contact us at: [email protected]