MsgBox & Data Validation icons


0. Quick guide


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


  1. The MsgBox function Quick Info lists the arguments

    • MsgBox(Prompt, [Buttons As VbMsgBoxStyle = vbOKOnly],[Title],[HelpFile],[Context]) As VbMsgBoxResult

  2. The VbMsgBoxStyle enumerations include four enumerated constants (16, 32, 48 and 64) for icons:

    • vbCritical (16)
    • vbQuestion (32)
    • vbExclamation (48)
    • vbInformation (64)

  3. 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.


IconConstant (value)Description
vbCritical vbCritical (16) Displays the Critical Message icon - stop sign
vbQuestion vbQuestion (32) Displays the Warning Query icon - a question mark
vbExclamation vbExclamation (48) Displays the Warning Message icon - an exclamation point
vbInformation vbInformation (64) Displays the Information Message icon


The names of the constants are displayed in the VbMsgBoxStyle Quick Info list (figure 1).


xlf-vbmsgboxstyle-enumerations
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.


xlf-data-validation-error-alert
Fig 2: Data Validation > Error Alert tab showing - Style; Title; and Error message: settings

IconStyle AlertStyle (value)Description
dvStop Stop xlValidAlertStop (1) Displays the Stop icon
dvWarning Warning xlValidAlertWarning (2) Displays the Warning icon - an exclamation point
dvInformation Information xlValidAlertInformation (3) Displays the Information icon


xlf-xldvalertstyle-enumerations
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.


xlf-mbox-and-datav
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