Message Box

VBA Message Box

VBA stands for Visual Basic for Applications, an event-driven programming language from Microsoft. It is mostly used with Microsoft Office applications such as MSExcel, MS-Word, and MS-Access.

It assists techies in creating unique applications and solutions to improve such applications’ functionality. The benefit of this feature is that you DO NOT NEED to have Visual Basic installed on your computer; although, doing so will help you accomplish the goal.

With the number of built-in functions in Microsoft Excel, you may be wondering why you should use VBA in Excel. Only basic built-in functions are available in MS Excel, which may not be enough to carry out complex calculations. VBA becomes the most obvious answer in these situations.

Syntax

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

Parameter

Prompt − A Required Parameter. A String that is displayed as a message in the dialog box. The maximum length of prompt is approximately 1024 characters. If the message extends to more than a line, then the lines can be separated using a carriage return character (Chr(13)) or a linefeed character (Chr(10)) between each line.

Buttons − A parameter that is optional. A numeric expression that defines the message box’s method, the default button’s identity, the icon style to be used, and the kind of buttons to be displayed. Buttons have a default value of 0 if left empty.

Title − A parameter that is optional. The dialogue box’s title bar shows a String expression. The application name appears in the title bar if the title is blank.

Helpfile − A parameter that is optional. A String expression that specifies which Help file should be used to supply the dialogue box with context-sensitive help.

Context − A parameter that is optional. An expression in numbers that indicates the Help context number that the Help author has allocated to the relevant Help topic. Helpfiles must be included if context is given.

The `Buttons` parameter in the VBA `Message Box`

The Buttons parameter in the VBA MsgBox function is an optional argument that allows you to customize the appearance and behavior of the message box, including the type of buttons displayed.

Here’s a breakdown of the Buttons parameter:

You can combine these constants to create different button combinations and customize the appearance of the message box.

Example

				
					Sub MessageBox_button_Demo()
Dim result As Integer
result = MsgBox("Do you want to continue?", vbYesNoCancel + vbQuestion)

Select Case result
    Case vbYes
        ' User clicked Yes
    Case vbNo
        ' User clicked No
    Case vbCancel
        ' User clicked Cancel
End Select 
End Sub

Any of the following values can be entered into the Buttons parameter:

Below values are logically segregated into four groups:

The first group is (0 to 5) indicates the buttons to be displayed in the message box.
The second group (16, 32, 48, 64) describes the style of the icon to be displayed.
third group (0, 256, 512, 768) indicates which button must be the default.
fourth group (0, 4096) determines the modality of the message box.

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

16 vbCritical – Displays Critical Message icon.

32 vbQuestion – Displays Warning Query icon.

48 vbExclamation – Displays Warning Message icon.

64 vbInformation – Displays Information Message icon.

0 vbDefaultButton1 – First button is default.
256 vbDefaultButton2 – Second button is default.
512 vbDefaultButton3 – Third button is default.
768 vbDefaultButton4 – Fourth button is default.

0 vbApplicationModal Application modal – The current application will not work until the user responds to the message box.

4096 vbSystemModal System modal – All applications will not work until the user responds to the message box.

Example For First Group (0 to 5)

This VBA code iterates through the button values from 0 to 5, displays a message box with the corresponding button configuration, and logs the result in the Immediate Window.

				
					Sub ShowButtonValues()
    Dim buttonValue As Integer
    Dim message As String

    For buttonValue = 0 To 5
        Select Case buttonValue
            Case 0
                message = "OK button only"
            Case 1
                message = "OK and Cancel buttons"
            Case 2
                message = "Abort, Retry, and Ignore buttons"
            Case 3
                message = "Yes, No, and Cancel buttons"
            Case 4
                message = "Yes and No buttons"
            Case 5
                message = "Retry and Cancel buttons"
        End Select

        MsgBox message, buttonValue
    Next buttonValue
End Sub

Example For Second Group (16 to 64)

This code demonstrates how to use the vbCritical, vbQuestion, vbExclamation, and vbInformation constants to display different message box icons. Each message box will display the specified icon along with the corresponding text message.

				
					Sub ShowMessageIcons()
    Dim msg As String
    Dim icon As Integer

    ' Critical Message
    msg = "This is a critical message."
    icon = 16 'you can write 16 or vbCritical
    MsgBox msg, icon

    ' Warning Query - Question Mark
    msg = "Are you sure you want to proceed?"
    icon = 32 'you can write 32 or vbQuestion
    MsgBox msg, icon

    ' Warning Message
    msg = "Warning! This operation may have unintended consequences."
    icon = 48 'you can write 48 or bExclamation
    MsgBox msg, icon

    ' Information Message
    msg = "This is an informative message."
    icon = 64 'you can write 64 or vbInformation
    MsgBox msg, icon
End Sub

Example For Third Group (0, 256, 512, 768)

This code demonstrates how to use the vbDefaultButton constants to specify the default button in a message box. By combining the buttons and defaultButton parameters, you can control the displayed buttons and the default button.

				
					Sub MessageBox_Demo3()
    Dim msg As String
    Dim buttons As Integer
    Dim defaultButton As Integer

    ' First button as default
    msg = "This is a message with the first button as default."
    buttons = vbOKCancel
    defaultButton = vbDefaultButton1
    MsgBox msg, buttons, defaultButton

    ' Second button as default
    msg = "This is a message with the second button as default."
    buttons = vbAbortRetryIgnore
    defaultButton = vbDefaultButton2
    MsgBox msg, buttons, defaultButton

    ' Third button as default
    msg = "This is a message with the third button as default."
    buttons = vbYesNoCancel
    defaultButton = vbDefaultButton3
    MsgBox msg, buttons, defaultButton

    ' Fourth button as default
    msg = "This is a message with the fourth button as default."
    buttons = vbRetryCancel
    defaultButton = vbDefaultButton4
    MsgBox msg, buttons, defaultButton

End Sub

Example For Forth Group (0, 4096)

Application Modal:
- The user must respond to the message box before they can interact with any other part of the application.
- Other windows in the application can still be resized or moved, but they cannot be activated.
System Modal:
- The user must respond to the message box before they can interact with any other application on the system.
- All other applications are blocked until the message box is dismissed.

By combining the appropriate button and modality flags, you can create different types of message boxes to suit your specific needs.

				
					Sub ShowModalMessageBoxes()
    Dim msg As String

    ' Application modal message box
    msg = "This is an application modal message box."
    MsgBox msg, vbOKOnly + vbApplicationModal

    ' System modal message box
    msg = "This is a system modal message box."
    MsgBox msg, vbOKOnly + vbSystemModal
End Sub

Syntax

Parameter

The Buttons parameter in the VBA Message Box

The `Buttons` parameter in the VBA `Message Box`