The VBA MsgBox function is used to display messages to the user in the form of a message box.
We can configure the message box to provide the user with a number of different buttons such as Yes, No, Ok, Retry, Abort, Ignore and Cancel. The MsgBox function will then return the button that was clicked.
Contents
Related Links
Basic VBA MsgBox Examples
In most cases, you will use MsgBox to simply display a message or to ask the user to click Yes/No or Ok/Cancel. The following code shows how to display a simple message box:
' https://excelmacromastery.com/ Sub BasicMessageBox() ' Basic message MsgBox "There is no data on this worksheet " ' Basic message with "Error" as the title MsgBox "There is no data on this worksheet ", , "Error" End Sub
VBA MsgBox Parameters
The parameters of the message box are as follows:
MsgBox prompt, [ buttons, ] [ title, ] [ helpfile, context ]
prompt – This is the message text that will be displayed.
buttons[optional] – This parameter does many things including setting the buttons, icons, select button, modal type etc. If this parameter is not used a message box with the Ok button and no icon is displayed. See the next section for more about this parameter.
title[optional] – this is the title that will appear at the top of the message box. The default is “Microsoft Excel”.
helpfile, context[optional] – These parameters are used to reference a help file and location of specific help text. It is very unlikely you use this unless you are creating an application for a third party and help files are a requirement.
VBA MsgBox Return Values
The following are all the return values for the MsgBox function:
vbOk
vbCancel
vbAbort
vbRetry
vbIgnore
vbYes
vbNo
Each of these values represents a button that was clicked.
VBA MsgBox Yes No
We can use the message box to get a simple response from the user. For example, we can ask the user a question and they can respond by clicking on the Yes or No button. The return value from the MsgBox function tells us which button was clicked.
If we want to get a Yes/No response from the user we can do it with the following code:
' https://excelmacromastery.com/ Sub MessagesYesNoWithResponse() ' Display a messagebox based on the response If MsgBox("Do you wish to continue? ", vbYesNo) = vbYes Then MsgBox "The user clicked Yes" Else MsgBox "The user clicked No" End If End Sub
Note: When we return a value from the message box we must use parenthesis around the parameters or we will get the “Expected end of statement” error.
We can also use a variable to store the response from the MsgBox. We would normally do this if we want to use the response more than once. For example, if there were three buttons:
' https://excelmacromastery.com/ Sub Msgbox_AbortRetryIgnore() Dim resp As VbMsgBoxResult ' Store MsgBox response in a variable resp = MsgBox("Do you wish to continue? ", vbAbortRetryIgnore) ' Display Ok/Cancel buttons and get response If resp = vbAbort Then MsgBox "The user clicked Abort" ElseIf resp = vbRetry Then MsgBox "The user clicked Retry" ElseIf resp = vbIgnore Then MsgBox "The user clicked Ignore" End If End Sub
VBA MsgBox Button Constants
The button parameter of MsgBox allows us to configure the message box in many ways. The table below shows the different options:
| Constant | Group | Type | Description |
|---|---|---|---|
| vbOKOnly | 1 | Buttons | Ok button. |
| vbOKCancel | 1 | Buttons | Ok and cancel buttons. |
| vbAbortRetryIgnore | 1 | Buttons | Abort, Retry and Ignore buttons. |
| vbYesNoCancel | 1 | Buttons | Yes, No and Cancel buttons. |
| vbYesNo | 1 | Buttons | Yes and No buttons. |
| vbRetryCancel | 1 | Buttons | Retry and Cancel buttons. |
| vbCritical | 2 | Icon | Critical Message icon. |
| vbQuestion | 2 | Icon | Question mark icon. |
| vbExclamation | 2 | Icon | Warning Message icon. |
| vbInformation | 2 | Icon | Information Message icon. |
| vbDefaultButton1 | 3 | Default button | Set button 1 to be selected. |
| vbDefaultButton2 | 3 | Default button | Set button 2 to be selected. |
| vbDefaultButton3 | 3 | Default button | Set button 3 to be selected. |
| vbDefaultButton4 | 3 | Default button | Set button 4 to be selected. Note that there will only be four buttons if the help button is included with vbAbortRetryIgnore or vbYesNoCancel. |
| vbApplicationModal | 4 | Modal | Cannot access Excel while the button is displayed. Msgbox is only displayed when Excel is the active application. |
| vbSystemModal | 4 | Modal | Same as vbApplicationModal but the message box is displayed in front of all applications. |
| vbMsgBoxHelpButton | 5 | Other | Adds a help button |
| vbMsgBoxSetForeground | 5 | Other | Sets the message box windows to be the foreground window |
| vbMsgBoxRight | 5 | Other | Right aligns the text. |
| vbMsgBoxRtlReading | 5 | Other | Specifies text should appear as right-to-left reading on Hebrew and Arabic systems. |
These constants work as follows:
- The constants in group 1 are used to select the buttons.
- The constants in group 2 are used to select icons.
- The constants in group 3 are used to select which button is highlighted when the message box appears.
- The constants in group 4 are used to set the modal type of the message box.
- The constants in group 5 are used for various settings.
When we use MsgBox, we can combine items from each group by using the plus sign. For example:
MsgBox "Example 1" ,vbOkCancel + vbCritical + vbDefaultButton1 + vbApplicationModal
This displays the message box with the Ok and Cancel button, the critical message icon, with the Ok button highlighted and the message box will display only when Excel is the active application.
MsgBox "Example 2", vbYesNo + vbQuestion + vbDefaultButton2 + vbSystemModal
This displays the message box with the Yes and No button, the warning query icon, with the No button highlighted and the message box will display in front of all applications.
Important: Each time we use the MsgBox function we can only select one of each:
- button type
- icon type
- default button
- modal type
In other words, we can only select one item from each of the first 4 groups.
The next section shows some more examples of using the message box.
VBA MsgBox Examples
The following examples show to display the various icons with the Yes and No buttons:
' Yes/No buttons with Critical icon and No button selected resp = MsgBox("Do you wish to continue", vbYesNo + vbCritical) ' Yes/No buttons with Warning Query icon and Yes button selected resp = MsgBox("Do you wish to continue", vbYesNo + vbQuestion) ' Yes/No buttons with Warning Message icon and Yes button selected resp = MsgBox("Do you wish to continue", vbYesNo + vbExclamation) ' Yes/No button with Information Message icon and Cancel button selected resp = MsgBox("Do you wish to continue", vbYesNo + vbInformation)
The following examples show the Abort/Retry/Ignore button plus the help button with different buttons selected:
' Abort/Retry/Ignore button with the Help button displayed and Abort selected resp = MsgBox("Error", vbAbortRetryIgnore + vbDefaultButton1 + vbMsgBoxHelpButton) ' Abort/Retry/Ignore button with the help button displayed and Retry selected resp = MsgBox("Error", vbAbortRetryIgnore + vbDefaultButton2 + vbMsgBoxHelpButton) ' Abort/Retry/Ignore button with the Help button displayed and Ignore selected resp = MsgBox("Error", vbAbortRetryIgnore + vbDefaultButton3 + vbMsgBoxHelpButton) ' Abort/Retry/Ignore button with the Help button displayed and Help selected resp = MsgBox("Error", vbAbortRetryIgnore + vbDefaultButton4 + vbMsgBoxHelpButton)
The following examples show some button selections and the title parameter being set:
' Retry/Cancel button with query warning as the icon and "Error" as the title resp = MsgBox("An error occurred. Try again?", vbRetryCancel + vbQuestion, "Error") ' Ok button with critical icon and "System error" as the title MsgBox "An error occurred", vbCritical, "System Error"
What’s Next?
Free VBA Tutorial If you are new to VBA or you want to sharpen your existing VBA skills then why not try out the The Ultimate VBA Tutorial.
Related Training: Get full access to the Excel VBA training webinars and all the tutorials.
(NOTE: Planning to build or manage a VBA Application? Learn how to build 10 Excel VBA applications from scratch.)
Is it possible to define alternative text that goes into the buttons, even though they would act as vbYesNo still?
I’m working on a macro to copy charts and ranges to PowerPoint from Excel.
I want 2 options, one to copy and paste them as they are on new blank sheets, or for the user to be able to use a control sheet where each chart/range gets pasted to a particular slide and resized according to what’s on the control page.
I could use a vbYesNo message box but would like to specify the text on the buttons rather than say would you like to use a control page Yes or No
Hi Johnny,
If you want to configure more than the basic options then you need to create your own message box using UserForms.
-Paul
I have been searching the web to find advice on MsgBox.
I have a VBA subroutine
Private Sub Workbook_Open()
and inside this sub I have msgbox()
This works but I would really like to format (color, font size, underline words etc.) this msgbox because the purpose here is to show a mess (via msgbox) when opening up an excel file.
Could you provide a link or advice on how this can be done?
Many thanks
Hi Charles,
You need to create your own message box using UserForms.
-Paul
Thanks Paul,
I’ll have to search the web for the syntax needed to put into such a form.
Hey Johnny,
wellsr.com came up with a solution just for that. It works really well for me. https://wellsr.com/vba/2019/excel/create-advanced-vba-msgbox-custom-buttons/
But yes, if you want to get any more intricate than two buttons you will need to make a UserForm.
The button text for MsgBox is hardcoded and associated with each of the “vb” constants (e.g., vbOKOnly) that Paul mentioned in his article.
As far as I know, if you want full control over button text, you will need to create your own dialog/message box via a UserForm object.
If you use MsgBox a lot, have a need to clarify to the end-user what the buttons pertain to, or need a way to know what button was pressed to control the flow of processing, you may want to package MsgBox into a function. Below is a VBScript example of a function named AskUser() that I wrote and use in my scripts.
The variable name prefixes (e.g., int, lng, str) should give you a clue as to the VBA datatypes to use. WScript.Quit() is not appropriate for VBA code.
‘I am only showing definitions for the main variables that are needed
‘to interface directly with the AskUser() function below
Dim lngButtons
Dim strPrompt
Dim strTitle
Dim intSelection
‘The following code snippet references other variables (intExitErrorLevel,
‘lngInx, lngMetricFilesHidden, lngMetricFilesDeleted) and
‘objects (dictHiddenFiles, objFile)
strPrompt = “FILE” & vbCrLf & _
” ” & dictHiddenFiles.Keys()(lngInx – 1) & vbCrLf & vbCrLf & _
“TYPE” & vbCrLf & _
” ” & objFile.Type & vbCrLf & vbCrLf & _
“Click Cancel to exit script.”
lngButtons = vbYesNoCancel + vbQuestion + vbDefaultButton2
strTitle = “Delete this hidden file?” & ” (” & lngInx & ” of ” & lngMetricFilesHidden & “)”
intSelection = AskUser(strPrompt, lngButtons, strTitle)
Select Case intSelection
Case vbCancel
WScript.Quit(intExitErrorLevel)
Case vbYes
lngMetricFilesDeleted = lngMetricFilesDeleted + 1
objFile.Delete(True)
Case vbNo
‘Ignore — do nothing
Case Else
MsgBox _
“Unexpected user response of ” & intSelection & _
” was received.” & _
“”, vbOKOnly, “Error”
WScript.Quit(intExitErrorLevel)
End Select
Function AskUser(ByVal strPrompt, _
ByVal lngButtons, _
ByVal strTitle)
AskUser = MsgBox(strPrompt, lngButtons, strTitle)
End Function
Thank You With Smile
And it is also good to know that there is a hidden Copy-Handler active behind the scenes every time we see a Windows MessageBox popping up. Pressing Ctrl+C copies all MessageBox contents into the clipboard, ready to get pasted into some regular ASCII text email. No more: “Send us a screenshot,” Mr. Helpdesk Guy, please! 😉
Hi!. I have tried a macro for my Powerpoint. In Test mode it works perfectly, but in Presenter Mode the Msg Box doesn’t appear. Is there any advice you have for this? Thanks!
msgbox does not work in my macro unless I prefix it with VBA.
example VBA.Msgbox
Does anyone know why?
Are you getting an Error? It is most likely a conflict with another library.
Hi, can you run this as .exe or make a like a program so you can click open and use it. I am talking about saving msgbox like a program that you can run on PC. Without using visual studio to run the code. Thank you for your help!
You need to use a third-party tool to do this.
Paul, thanks so much for posting this info. Much appreciated!
Thanks Don
Nice Paul. But I have 1 question I’m not able to solve nor able to find its solution the on Internet.
My code is below (not telling function here as it is very long)
Public Function NSEDataCall (website, setCookies) As Stringe
……………..
………….
………
……
…
End Function
‘ My Macro
Sub GetNSECookies()
Dim website As String
Dim cookieValues As String
Dim website2 As String
Dim cookieValuesFinal As String
‘ First call
website = “https://www.nseindia.com/market-data/securities-lending-and-borrowing”
cookieValues = NSEDataCall (website, cookieValues)
‘ Debug.Print (cookieValues)
‘ Second call for sv_bm cookie
website = “https://www.nseindia.com/market-data/securities-lending-and-borrowing”
cookieValues = NSEDataCall (website, cookieValues)
Debug.Print (cookieValues)
End Sub
——- code end ——–
You see the result of this macro is shown in debug.print in an immediate window. But I want that result of my macro into the A1 cell of sheet1. Could you please give some help or give me resources to find how to do it.
I’m new to VBA but learning constantly and above code, I just arranged from different sites and a little modified.
ThisWorkbook.Worksheets(“Sheet1”).Range(“A1”) = cookieValues
I thought I found this years ago, but darned if I can find it now.
I’m debugging some code in my macro and want to keep to a minimum the number of msgboxes I have to click through. So I want to do something like this:
Msgbox “x is ” & x ‘ [don’t display msgbox at this point but continue the prompt below]
.
.
vba code
.
.
Msgbox “, now x is ” & x ‘ [now display the msgbox]
Is there a command I can use in the first msgbox to accomplish this?
thanks,
Is there a way, in PowerPoint VBA, to get the message box to list the slide and text box where the error occurred?