How to Customize Message Boxes in Excel 2016 VBA
The flexibility of the VBA buttons argument makes it easy to customize your Excel message boxes. You can choose which buttons to display, determine whether an icon appears, and decide which button is the default (the default button is “clicked” if the user presses Enter).
You can use built-in constants for the buttons argument. If you prefer, you can use the value rather than a constant (but using the built-in constants is a lot easier).
To use more than one of these constants as an argument, just connect them with a + operator. For example, to display a message box with Yes and No buttons and an exclamation icon, use the following expression as the second MsgBox argument:
vbYesNo + vbExclamation
Or, if you prefer to make your code less understandable, use a value of 52 (that is, 4 + 48).
The following example uses a combination of constants to display a message box with a Yes button and a No button (vbYesNo) as well as a question-mark icon (vbQuestion). The constant vbDefaultButton2 designates the second button (No) as the default button — that is, the button that is clicked if the user presses Enter. For simplicity, these constants are assigned to the Config variable and then use Config as the second argument in the MsgBox function:
Sub GetAnswer3() Dim Config As Long Dim Ans As Integer Config = vbYesNo + vbQuestion + vbDefaultButton2 Ans = MsgBox(“Process the monthly report?”, Config) If Ans = vbYes Then RunReport End Sub
Check out the message box Excel displays when you execute the GetAnswer3 procedure. If the user clicks the Yes button, the routine executes the procedure named RunReport. If the user clicks the No button (or presses Enter), the routine ends with no action. Because the title argument was omitted in the MsgBox function, Excel uses the default title, Microsoft Excel.
The following routine provides another example of using the MsgBox function:
Sub GetAnswer4() Dim Msg As String, Title As String Dim Config As Integer, Ans As Integer Msg = “Do you want to process the monthly report?” Msg = Msg & vbNewLine & vbNewLine Msg = Msg & “Processing the monthly report will “ Msg = Msg & “take approximately 15 minutes. It “ Msg = Msg & “will generate a 30-page report for “ Msg = Msg & “all sales offices for the current “ Msg = Msg & “month.” Title = “XYZ Marketing Company” Config = vbYesNo + vbQuestion Ans = MsgBox(Msg, Config, Title) If Ans = vbYes Then RunReport End Sub
This example demonstrates an efficient way to specify a longer message in a message box. A variable (Msg) and the concatenation operator (&) are used to build the message in a series of statements. The vbNewLine constant inserts a line-break character that starts a new line (use it twice to insert a blank line). The title argument is used to display a different title in the message box. Here is the message box Excel displays when you execute this procedure.
You can use constants (such as vbYes and vbNo) for the return value of a MsgBox function. Here are a few other constants.
|Constant||Value||What It Means|
|vbOK||1||User clicked OK.|
|vbCancel||2||User clicked Cancel.|
|vbAbort||3||User clicked Abort.|
|vbRetry||4||User clicked Retry.|
|vbIgnore||5||User clicked Ignore.|
|vbYes||6||User clicked Yes.|
|vbNo||7||User clicked No.|
And that’s pretty much all you need to know about the MsgBox function. Use message boxes with caution, though. There’s usually no reason to display message boxes that serve no purpose. For example, people tend to get annoyed when they see a message box every day that reads Good morning. Thanks for loading the Budget Projection workbook.