Syntax of MsgBox function in VBA:
The syntax of VBA Msgbox is as follows:
MsgBox (Text_String [, buttons] [, title] [, helpfile, context])
Here Text_String is the message that you want the msgbox to display. The maximum length of Text_String is 1024
characters.
buttons parameter specifies the type of buttons and icon that you want to be shown on the message box. It is an
optional parameter. If you omit it then msgbox shows the default vbOKOnly button.
title refers to the text displayed in the title bar of message box. This is an optional parameter.
helpfile is a string parameter that specifies the help file to be used for the dialog box. It is also an optional parameter
but it becomes mandatory if context parameter is to be used.
context is a numeric parameter that specifies the number assigned to the appropriate Help topic. It is an optional
parameter but it becomes mandatory if helpfile parameter is used.
Different values of buttons parameter in Msgbox:
In VBA message box, buttons parameter can have following values:
Constant
* Value
Description
vbOKOnly
It displays a single OK button
vbOKCancel
It displays two buttons OK and Cancel.
vbAbortRetryIgnore
It displays three buttons Abort, Retry, and Ignore.
vbYesNoCancel
It displays three buttons Yes, No, and Cancel.
vbYesNo
It displays two buttons Yes and No.
vbRetryCancel
It displays two buttons Retry and Cancel.
vbCritical
16
It displays a Critical Message icon.
vbQuestion
32
It displays a Query icon.
vbExclamation
48
It displays a Warning Message icon.
vbInformation
64
It displays an Information Message icon.
vbDefaultButton1
First button is treated as default.
vbDefaultButton2
256
Second button is treated as default.
vbDefaultButton3
512
Third button is treated as default.
vbDefaultButton4
768
Fourth button is treated as default.
This suspends the current application till the user responds to
the message box.
4096
This suspends all the applications till the user responds to the
message box.
16384
This adds a Help button to the message box.
vbApplicationModal
vbSystemModal
vbMsgBoxHelpButton
VbMsgBoxSetForeground 65536
Ensures that message box window is foreground.
vbMsgBoxRight
524288
This sets the Text to right aligned
vbMsgBoxRtlReading
1048576 This option specifies that text should appear as right-to-left.
* Ditambahkan dari sumber laen
DeJOKO
Examples of Message boxes in VBA:
Now, lets move on to some particle examples to understand the use of msgbox:
Example 1: Basic message box:
Sub Msg_exe()
MsgBox "This is a message!"
End Sub
Example 2: Basic message box:
Sub Msg_exe()
MsgBox "This is a message!", , "This is the Title"
End Sub
Example 3: Exclamation message box:
Sub Msg_exe()
MsgBox "This is a message!", vbExclamation, "This is the Title"
End Sub
Example 4: Message box with multiple lines
Sub Msg_exe()
MsgBox "Message Line 1" & vbCrLf & "Message Line 2", , "This is the Title"
End Sub
Note 1: Here vbCrLf is the new line character in VBA. It can also be replaced by vbNewLine.
Example 5: Critical Message box with three buttons
Sub Msg_exe()
result = MsgBox("Critical Error Encountered", vbAbortRetryIgnore + vbCritical, "Error Encountered")
End Sub
DeJOKO
Note 1: Here I have used two values for the button parameter separated by a + sign.
Note 2: You will notice that here I have used a variable result for accepting the value returned by Msgbox.
Values returned by MsgBox Function:
As I have foretold that VBA MsgBox function returns a value based on the user input. These values can be anyone of
the below ones:
Value
Description
1
Specifies that OK button is clicked.
Specifies that Cancel button is clicked.
Specifies that Abort button is clicked.
Specifies that Retry button is clicked.
Specifies that Ignore button is clicked.
Specifies that Yes button is clicked.
Specifies that No button is clicked.
How you can decide program flow based on the values returned by Message
Box?
In the above table you can see that VBA Msgbox function returns some integer values corresponding to button clicked
on the dialog box. You can check this number using an IF Statement or by using a Select case statement.
In the below example I have done the same:
Sub Msg_exe()
Dim Ret_type As Integer
Dim strMsg As String
Dim strTitle As String
' Dialog Message
strMsg = "Click any one of the below buttons."
' Dialog's Title
strTitle = "Some Title"
'Display MessageBox
Ret_type = MsgBox(strMsg, vbYesNoCancel + vbQuestion, strTitle)
' Check pressed button
Select Case Ret_type
Case 6
MsgBox "You clicked 'YES' button."
Case 7
MsgBox "You clicked 'NO' button."
Case 2
MsgBox "You clicked 'CANCEL' button."
End Select
End Sub
So, this was all about the msgbox function in VBA. Do let me know if you have any queries related to the topic.
Ankit Kaul : http://www.exceltrick.com/formulas_macros/vba-msgbox/
DeJOKO
