The VBA MsgBox() function allows a choice of an optional icon from a list of four. The icon is displayed to the left of the Prompt.
Icons are part of the Buttons arguments as type VbMsgBoxStyle enumerations with default value 0 (vbOkOnly)
1. The MsgBox() function and VbMsgBoxStyle enumerations
The MsgBox function Quick Info lists the arguments
MsgBox(Prompt, [Buttons AsVbMsgBoxStyle = vbOKOnly],[Title],[HelpFile],[Context]) As VbMsgBoxResult
The VbMsgBoxStyle enumerations include four enumerated constants (16, 32, 48 and 64) for icons:
vbCritical (16)
vbQuestion (32)
vbExclamation (48)
vbInformation (64)
Icon images are shown in the next section
2. MsgBox enumerated constant icons
Icons are optional. The MsgBox function can display a maximum of one icons from the list.
Icon
Constant (value)
Description
vbCritical (16)
Displays the Critical Message icon - stop sign
vbQuestion (32)
Displays the Warning Query icon - a question mark
vbExclamation (48)
Displays the Warning Message icon - an exclamation point
vbInformation (64)
Displays the Information Message icon
The names of the constants are displayed in the VbMsgBoxStyle Quick Info list (figure 1).
Fig 1: VbMsgBoxStyle enumerations showing - vbCritical (value 16) as third on the list
3. Data validation Error Alert Style icons
The Excel data validation tool allows the user to compose a custom message box from properties on the Data Validation > Error Alert tab (see figure 2). Three icons are available. The MsgBox vbQuestion icon has been dropped from the list.
Fig 2: Data Validation > Error Alert tab showing - Style; Title; and Error message: settings
Icon
Style AlertStyle (value)
Description
Stop xlValidAlertStop (1)
Displays the Stop icon
Warning xlValidAlertWarning (2)
Displays the Warning icon - an exclamation point
Information xlValidAlertInformation (3)
Displays the Information icon
Fig 3: XlDVAlertStyle enumerations showing the three available constants
The message boxes from MsgBox() function, and the Data Validation Error Alert are compared in figure 3.
Fig 3: Message Boxes: MsgBox() function (left) - code 1, and Data Validation > Error Alert (right) - code 2 Code 1: xlfDay replicates the Data Validation Error Alert box from figure 2 Sub xlfDay() Dim Prompt As String, Title As String Dim Buttons As Integer, Response As Integer, DblLnBreak As String DblLnBreak = vbNewLine & vbNewLine Prompt = "Enter date in the range :: 1 to 31 only" & DblLnBreak & _ "See online Help for Details of Data Validation restrictions" Title = "xlf Day :: MsgBox" Buttons = VbMsgBoxStyle.vbRetryCancel + _ VbMsgBoxStyle.vbMsgBoxHelpButton + _ VbMsgBoxStyle.vbCritical Response = MsgBox(Prompt, Buttons, Title) ' Help file, and Context omitted ' Cell value rules not coded End Sub Lines 12 to 14 can be written as: ' alternate version of the Button assignment statement without the VbMsgBoxStyle identifier Buttons = vbRetryCancel + vbMsgBoxHelpButton + vbCritical Code 2: xlfDayDV sets Data Validation Error Alert box from figure 2. The Data Validation cell must be the Selection object Sub xlfDayDV() Dim Prompt As String, Title As String Dim Buttons As Integer, Response As Integer, DblLnBreak As String DblLnBreak = vbNewLine & vbNewLine Prompt = "Enter date in the range :: 1 to 31 only" & DblLnBreak & _ "See online Help for Details of Data Validation restrictions" Title = "xlf Day :: Data Validation" With Selection.Validation .Delete .Add Type:=xlValidateWholeNumber, _ AlertStyle:=xlValidAlertStop, _ Operator:=xlBetween, _ Formula1:="1", _ Formula2:="31" .IgnoreBlank = True .InCellDropdown = True .ErrorTitle = Title .InputMessage = "" .ErrorMessage = Prompt End With End Sub
Development platform: Excel 2016 (64 bit) Office 365 ProPlus and VBA 7.1
Published: 3 October 2017
Revised: Saturday 25th of February 2023 - 10:12 AM, [Australian Eastern Time (AET)]
