MsgBox function in VBA

MsgBox function in VBA


Previous articles readers have read with Taimienphi.vn to learn about the TRIM and DIR functions in VBA, the following article Taimienphi.vn will introduce you more about the MsgBox function in VBA.


In VBA, we can use MsgBox function To display the message box as shown below:

Jaw MsgBox is only used to display a dialog box that informs the user with custom messages or some basic input notifications (such as Yes / No / OK / Cancel).

When the MsgBox dialog is displayed, the VBA code will be paused. We will have to click on any button in MsgBox to run the remaining VBA code.

Refer to the following article of Taimienphi.vn to learn about the MsgBox function in VBA, formulas and how to use functions.

Structure of MsgBox notification box in VBA.

A message box includes the following:

ham msgbox in vba 2

1. Title (title): used to display notification content in the notification box. If you do not specify anything, in this case it will display the application name as Excel.

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

3. Button (button): OK button is the default, but if desired we can customize it to display other buttons such as Yes / No, Yes / No / Cancel, Retry / Ignore, ….

4. Close icon: to close the message box, we click the close icon is done.

Syntax of the MsgBox function in VBA

The MsgBox function has the same syntax as other VBA functions:

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

Inside:

Promt (prompt): is a required argument. It displays the message we see in MsgBox. In the example above, the prompt is “This is a sample MsgBox”. The prompt is limited to 1024 characters, and can be used to display the values ​​of variables. In case you want to display a prompt that includes multiple lines, we can also do this.

– [buttons] (button): defines which buttons and icons are displayed in MsgBox. For example, if using vbOkOnly it will show the OK button, if using the vbOKCancel button it will display both buttons as OK and Cancel.

– Hàm MsgBox trong VBA (title): here we can specify which to display in the notification dialog, it will be displayed in the title bar of MsgBox. If you do not specify anything, it will display the application name.

– [helpfile]: We can specify the help file accessible when the user clicks the Help button. The Help button appears only when we use the button code. If using the Help file, we must also specify the context argument.

– [context]: is a numeric expression or Help context number assigned to the appropriate Help topic.

Note: All arguments in square brackets are optional. Only the “prompt” argument is required.

Button constants (Button) MsgBox in VBA.

In the next section, Taimienphi.vn will introduce you to the different types of buttons that can be used in MsgBox VBA.

Below is a list of button constants that can be used in MsgBox VBA:

ham msgbox in vba 3

Note: When the OK button is selected, the code will continue running, and if you click the Cancel button, the code will stop.

VbOKOnly button (default)

If only the prompt is used and no arguments are specified, the default message dialog box will look like this:

ham msgbox in vba 4

The following code snippet displays this message dialog box:

Sub DefaultMsgBox ()

MsgBox “This is a sample box”

End Sub

Note the text string must be enclosed in quotation marks.

In addition, we can also use the vbOKOnly button’s constants even without specifying any arguments, which is considered to be the default.

OK & Cancel button

If we just want to display the OK and Cancel buttons, we use the vbOKCancel constant.

ham msgbox in vba 5

Sub MsgBoxOKCancel ()

MsgBox “Want to Continue?”, VbOKCancel

End Sub

Button Abort, Retry and Ignore

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

ham msgbox in vba 6

The following code snippet displays this message dialog box:

Sub MsgBoxAbortRetryIgnore ()

MsgBox “What do you want to do?”, VbAbortRetryIgnore

End Sub

Yes and No buttons

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

Sub MsgBoxYesNo ()

MsgBox “Should we stop?”, VbYesNo

End Sub

ham msgbox in vba 7

The Retry and Cancel buttons

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

Sub MsgBoxRetryCancel ()

MsgBox “What do you want to do next?”, VbRetryCancel

End Sub

ham msgbox in vba 8

Help button

Use the vbMsgBoxHelpButton constant to display the Help button. Also we can use it with constants of other nodes.

Sub MsgBoxRetryHelp ()

MsgBox “What do you want to do next?”, VbRetryCancel + vbMsgBoxHelpButton

End Sub

ham msgbox in vba 9

Note: In the above code we combine 2 constants of 2 different nodes (vbRetryCancel and vbMsgBoxHelpButton). The first part shows the Retry and Cancel buttons, the second part shows the Help button.

Set default button

We can use the vbDefaultButton1 constant to set the first button as the default button. This means that the button has been selected, then we press Enter to execute that button.

Here is the code to set the 2nd button (No button) as the default button:

Sub MsgBoxOKCancel ()

MsgBox “What do you want to do next?”, VbYesNoCancel + vbDefaultButton2

End Sub

ham msgbox in vba 10

In most cases, the leftmost button is the default button. Alternatively we can select other buttons to lmaf the default button using vbDefaultButton2, vbDefaultButton3 and vbDefaultButton4.

The constants of icons (icons) of MsgBox in VBA

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

The following is a list of codes listing the corresponding symbols:

ham msgbox in vba 11

Important notification icon

If we want to display an important notification icon in the MsgBox notification dialog, we use the vbCritical constant. Also we can combine this constant with the constants of other symbols by adding the + sign between codes.

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

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

ham msgbox in vba 12

If you want to display an important notification icon and Yes and No buttons, use the code below:

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

ham msgbox in vba 13

Question mark icon

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

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

ham msgbox in vba 14

Warning icon

If we want to display an exclamation point icon (warning icon) in MsgBox, we use the constant vbExclamation.

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

ham msgbox in vba 15

Information icon

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

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

ham msgbox in vba 16

Customize the title and reminder in the MsgBox notification dialog box

By using MsgBox, we can customize the title (title) and prompt (prompt) in the notification dialog.
In the case if the title argument is not specified, MsgBox will automatically use the title of the application (in this case, Excel).
To specify the title you specify, use the code below:

Sub MsgBoxInformationIcon ()
MsgBox “Do you want to continue?”, VbYesNo + vbQuestion, “Step 1 of 3”
End Sub

ham msgbox in vba 17

Similarly, we can also customize the prompts in the notification, and we can add line breaks.

ham msgbox in vba 18

In the code below Taimienphi.vn 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

ham msgbox in vba 19

Alternatively, we can also use the return character – Chr (13) – or – Chr (10) to insert a new line into the reminder in the notification.

Note: We can only add a new line to the reminder, not the title of the notification.

Assign a MsgBox value to a variable

In case if clicking on any button but does not work.

With the MsgBox function in Excel, we can decide when a user clicks on a particular button whatever it will do. This is simply because each node has a related value.

If you click the Yes button, the MsgBox function will return the value (6 or vbYes constant) that we can use in our code. Similarly when clicking No, it will return another value (7 or vbNo) that we can use in the code.

The following is a list of the exact and constant values ​​returned by the MsgBox function:

ham msgbox in vba 20

Next Taimienphi.vn will show you how to control VBA macro code based on the button that a user clicks.

In the code below, if the user clicks Yes, it will display the message “you have clicked Yes”, and if you click No, it will display the message “you have 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

ham msgbox in vba 21

In the above code, Taimienphi.vn assigns the value of the MsgBox function to the Result variable. When you click the Yes button, the Result variable takes the vbYes constant (or number 6), when you click No, the Result variable takes the vbNo constant (or the number 7).

Then use the If Then Else structure to check if the Result variable holds the value vbYes. If yes, it will display the message “you have clicked Yes”, otherwise the message “you have clicked No”.

We can use the same concept to run code if the user clicks Yes, and exits the sub when clicking No.

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

For example, in the Result = MsgBox line (“Do you want to continue?”, VbYesNo + vbQuestion), we can see the arguments in parentheses.

https://thuthuat.taimienphi.vn/ham-msgbox-rong-vba-53298n.aspx
The above article Taimienphi.vn has just introduced you to the MsgBox function in VBA. Also readers can refer to some articles already on Taimienphi.vn to learn more about DIR function in VBA. If you have any questions or questions, you can leave them in the comment section below the article.

.

Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *