MsgBox

The MSGBOX function will display a built-in dialog box that can be used to display a message to the user and wait for a response.
This function is extremely useful and is often very handy when you are debugging.
This function returns a value which indicates what choice the user made.
This function can be inserted anywhere in your code to pause the program and display a message to the user.

alt text

Displaying a Question

This function is typically used to as a way of asking the user a question.

If MsgBox("save the file ?", VBA.vbMsgBoxStyle.vbYesNo) = VBA.vbMsgBoxResult.vbYes Then 
'save the file
End If

Displaying a Message

This function can also be used by ignoring the answer to just display a message to the user ignoring the returned value.

Call MsgBox("This file has been saved",VBA.vbMsgBoxStyle.vbOKOnly) 
Call MsgBox("This file has been saved") 'vbOkOnly is the default

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

promptRequired. String expression containing the message to be displayed. The maximum length of a single line prompt is approximately 1024 characters, depending on the width of the characters used. You can create a multi line message by using the carriage return character vbCrLf (Chr(13)), a linefeed character (Chr(10)), or carriage return-linefeed character combination (Chr(13) & Chr(10)) between each line.
buttonsOptional. Long numeric expression that is the sum 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 is 0, which is a VbOkOnly button
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.

If the Esc key is pressed then this is the same as the Cancel button being pressed.


VBA.vbMsgBoxResult

ConstantReturnedDescription
vbOk1User pressed the OK button.
vbCancel2User pressed the Cancel button or ESC key.
vbAbort3User pressed the Abort button.
vbRetry4User pressed the Retry button.
vbIgnore5User pressed the Ignore button.
vbYes6User pressed the Yes button.
vbNo7User pressed the No button.

VBA.vbMsgBoxStyle - Button and Icon Setttings

ConstantReturnedDescription
vbOKOnly0Display OK button only (this is the default).
vbOKCancel1Display OK and Cancel buttons.
vbAbortRetryIgnore2Display Abort, Retry, and Ignore buttons.
vbYesNoCancel3Display Yes, No, and Cancel buttons.
vbYesNo4Display Yes and No buttons.
vbRetryCancel5Display Retry and Cancel buttons.
vbCritical16Display Critical Message icon.
vbQuestion32Display Warning Query icon.
vbExclamation48Display Warning Message icon.
vbInformation64Display Information Message icon.
vbDefaultButton10First button is default.
vbDefaultButton2256Second button is default.
vbDefaultButton3512Third button is default.
vbDefaultButton4768Fourth button is default.

VBA.vbMsgBoxStyle - Modality

vbApplicationModal0The user must respond to the message box before continuing work in the current application.
vbSystemModal4096System modal; all applications are suspended until the user responds to the message box.
vbMsgBoxHelpButton16384Adds a Help button to the message box.
vbMsgBoxSetForeground65536Specifies the message box window as the foreground window.
vbMsgBoxRight524288Text is right aligned.
vbMsgBoxRtlReading1048576Specifies text should appear as right-to-left reading on Hebrew and Arabic systems.

Type Mismatch Error

Be careful when using this function as you can easily get a type mismatch when this line of code is executed.
The following line will compile however when it tries to execute it your code will be interupted with a "Type Mismatch" error.

Call MsgBox("Press any key to continue", "Macro Interrupted") 
alt text

There is no compile time checking of the arguments passed to this function so make sure you are passing the correct datatype for each of the arguments.
The correct line of code should have been:

Call MsgBox("Press any key to continue", , "Macro Interrupted") 
alt text

© 2024 Better Solutions Limited. All Rights Reserved. © 2024 Better Solutions Limited TopPrevNext