March 12, 2022

MsgBox Function

MsgBox Function in Excel is not only an interaction tool between the user and the computer but also a great debugging tool. Watch the video below:

The MsgBox function displays a message in a dialog box, waits for the user to click a button, and returns an Integer indicating which button the user clicked.

Syntax

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

The MsgBox function syntax has these named arguments:

PartDescription
promptRequired. String expression displayed as the message in the dialog box. The maximum length of prompt is approximately 1024 characters, depending on the width of the characters used. If prompt consists of more than one line, you can separate the lines by using a carriage return character (Chr(13)), a linefeed character (Chr(10)), or carriage return – linefeed character combination (Chr(13) & Chr(10)) or VBCrLf or vbNewLine between each line. 
buttonsOptional. Numeric expression that is the combination of values specifying the number and type of buttons to display, the icon style to use, the identity of the default button, and the modality of the message box. If omitted, the default value for buttons is 0. 
titleOptional. String expression displayed in the title bar of the dialog box. If you omit title, the application name is placed in the title bar. 
helpfileOptional. String expression that identifies the Help file to use to provide context-sensitive Help for the dialog box. If helpfile is provided, context must also be provided. 
contextOptional. Numeric expression that is the Help context number assigned to the appropriate Help topic by the Help author. If context is provided, helpfile must also be provided.
Arguments of the MsgBox Function

The buttons argument settings are:

ConstantValueDescription
vbOKOnly0Display OK button only.
vbOKCancel1Display OK and Cancel buttons.
vbAbortRetryIgnore                      2Display Abort, Retry, and Ignore buttons.
vbYesNoCancel                                3Display Yes, No, and Cancel buttons.
vbYesNo                                             4Display Yes and No buttons.
vbRetryCancel                                  5Display Retry and Cancel buttons.
vbCritical                             16Display Critical Message icon.
vbQuestion                                       32Display Warning Query icon.
vbExclamation                                  48Display Warning Message icon.
vbInformation                                  64Display Information Message icon.
vbDefaultButton1                           0First button is default.
vbDefaultButton2                           256Second button is default.
vbDefaultButton3                           512Third button is default.
vbDefaultButton4                           768Fourth button is default.
vbApplicationModal                      0Application modal; the user must respond to the message box before continuing work in the current application.
vbSystemModal                               4096System modal; all applications are suspended until the user responds to the message box.
vbMsgBoxHelpButton                   16384Adds Help button to the message box.
vbMsgBoxSetForeground65536Specifies the message box window as the foreground window.
vbMsgBoxRight                               524288Text is right-aligned.
vbMsgBoxRtlReading                     1048576Specifies text should appear as right-to-left reading on Hebrew and Arabic systems.
Button Arguments in the MsgBox Function

The first group of values (0-5) describes the number and type of buttons displayed in the dialog box; the second group (16, 32, 48, 64) describes the icon style; the third group (0, 256, 512) determines which button is the default; and the fourth group (0, 4096) determines the modality of the message box. When combining numbers to create a final value for the buttons argument, use only one number from each group.

Note

These constants are specified by Visual Basic for Applications. As a result, the names can be used anywhere in your code in place of the actual values.

Return values

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

Remarks

When both helpfile and context are provided, the user can press F1 (Windows) or HELP (Macintosh) to view the Help topic corresponding to the context. Some host applications, for example, Microsoft Excel, also automatically add a Help button to the dialog box.

If the dialog box displays a Cancel button, pressing the ESC key has the same effect as clicking Cancel. If the dialog box contains a Help button, context-sensitive Help is provided for the dialog box. However, no value is returned until one of the other buttons is clicked.

How to make ‘No’ vbDefaultButton2

The code to make ‘No’ the default button, i. e. if the user presses ‘Enter’ this button performs the action. This is useful in cases where you run a macro to delete data, for example.

MsgBox “My Message Box”, vbYesNoCancel + vbMsgBoxHelpButton + vbDefaultButton2, “My Message”, _”Help”, 1

Code of Module1:

Sub MyMessageBox()

‘MsgBox “My Message Box”, vbMsgBoxHelpButton, “My Message”, _
“Help”, 1

‘MsgBox “My Message Box”, vbMsgBoxHelpButton, “My Message”, _
“Help”, 1

‘MsgBox “My Message Box”, vbYesNo + vbMsgBoxHelpButton, “My Message”, _
“Help”, 1

‘If MsgBox(“My Message Box”, vbYesNo + vbMsgBoxHelpButton, “My Message”, _
“Help”, 1) = vbYes Then

‘UserForm1.Show

‘End If

Range(“A1”) = MsgBox(“My Message Box”, vbYesNo + vbMsgBoxHelpButton, “My Message”, _
“Help”, 1)

End Sub

Note: Code line with an apostrophe are inactive.

Module2:

Sub MsgBoxToDebug()
Dim lastrow As Long
Dim NextBlankRow As Long

Sheet2.Activate

MsgBox ActiveSheet.Name

lastrow = Application.WorksheetFunction.CountA(Sheet2.Range(“A:A”))

NextBlankRow = lastrow + 1

MsgBox “The lastrow used is: ” & lastrow & vbCrLf & “The next blank row is: ” & NextBlankRow

Cells(NextBlankRow, 1).Select

MsgBox ActiveSheet.Name

Cells(NextBlankRow, 1) = “ABC010”

End Sub

Module3:

Sub msgboxtodisplay()
MsgBox ActiveSheet.Name
End Sub

Macro code for Module4:

Sub checklastrow()

Dim lastrow As Long

‘Sheet3.Activate

lastrow = Sheets(“Sheet3”).Range(“A” & Rows.Count).End(xlUp).Row

MsgBox ActiveSheet.Name

MsgBox “The last row used is: ” & lastrow

Cells(lastrow + 1, 1).Select

MsgBox ActiveSheet.Name

End Sub

The above descriptive details are taken from the Microsoft Official Document on the MsgBox function.