MsgBox

This is 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.


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 csn also be used by ignoring the answer to just display a message to the user ignoring the returned value.
When you are not interesting in the return value it is better to use the Call keyword.

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
vbOk1 
vbCancel2 
vbAbort3 
vbRetry4 
vbIgnore5 
vbYes6 
vbNo7 


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") 

 


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") 

 



© 2017 Better Solutions Limited. All Rights Reserved. © 2017 Better Solutions Limited

Top

PrevNext