Custom Message Boxes

The MsgBox function provided by VBA is the most common way of interacting with the user.  While this function provides a lot of options, sometimes you want highly customized message to display, particular the button captions.  For that, you’ll need a userform.  This example shows how a userform can be used as an alternative to the MsgBox function.

Start with a userform with one label control and three commandbuttons


Set the Visible properties of the commandbuttons to False.  All other properties will be set in the Activate event.  Next, create Property Get and Property Let statements to set the message box properties.  These statements go in the userform’s module.

Private msTitle As String
Private msPrompt As String
Private mavButton As Variant
Private mlReturn As Long

‘Establish properties for msgbox
Property Get Title() As String
    Title = msTitle
End Property

Property Let Title(aTitle As String)
    msTitle = aTitle
End Property

Property Get Prompt() As String
    Prompt = msPrompt
End Property

Property Let Prompt(aPrompt As String)
    msPrompt = aPrompt
End Property

Property Get Buttons() As Variant
    Buttons = mavButton
End Property

Property Let Buttons(aButtons As Variant)
    mavButton = aButtons
End Property

The Buttons property is an array of captions for the three commandbuttons.  Now, create the Activate and QueryClose events in the useforms module

‘Set up the properties of the msgbox
Private Sub UserForm_Activate()

    Dim i As Long
    Me.Caption = msTitle
    Me.lblPrompt = msPrompt
    For i = LBound(mavButton) To UBound(mavButton)
        With Me.Controls(“cmd” & i + 1)
            .Caption = mavButton(i)
            .Visible = True
        End With
    Next i
End Sub

‘Force user to use buttons
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)

    If CloseMode = vbFormControlMenu Then
        Cancel = True
    End If
End Sub

And we’ll need a way to return a value like the built-in MsgBox function.  For that, create a read only property and use the click events of the commandbuttons.

‘Read only property for return value
Property Get ReturnValue() As Long
    ReturnValue = mlReturn
End Property

‘Hide the form to allow the functin to run and set the
‘return value
Private Sub cmd1_Click()
    mlReturn = CLng(Me.cmd1.Tag)
End Sub

Private Sub cmd2_Click()
    mlReturn = CLng(Me.cmd2.Tag)
End Sub

Private Sub cmd3_Click()
    mlReturn = CLng(Me.cmd3.Tag)
End Sub

Now, on to the UDF that controls this form.  The user (you) will pass arguments to the function similar to MsgBox.  The argument for the button captions is not optional like Title and Prompt, so that one has to go first.

Function CMsgBox(vaButtons As Variant, _
    Optional ByVal sPrompt As String = “Microsoft Excel”, _
    Optional ByVal sTitle As String) As Long
    ‘Validate inputs
    If Not IsArray(vaButtons) Then
        Exit Function
    End If
    If UBound(vaButtons) – LBound(vaButtons) + 1 > 3 Then
        Exit Function
    End If
    ‘Set msgbox properties
    Load UMsgBox
    UMsgBox.Title = sTitle
    UMsgBox.Prompt = sPrompt
    UMsgBox.Buttons = vaButtons
    CMsgBox = UMsgBox.ReturnValue
    Unload UMsgBox
End Function

The function returns zero if the Buttons argument is invalid.  Finally, an example sub that uses the function and what it looks like.

Sub CreateCustomMsg()

    Dim lResp As Long
    lResp = CMsgBox(Array(“Send”, “Don’t Send”, “Cancel”), _
        “Do you want to send the report?”, _
        “Send Report”)
    Select Case lResp
        Case 0
            MsgBox “Error calling message box”
        Case 1
            MsgBox “Report sent”
        Case 2
            MsgBox “Report NOT sent”
        Case 4
            MsgBox “User cancelled operation”
    End Select
End Sub


Not very fancy, I know.  The principles described here will allow you to create a message box with as much customization as a userform will allow.  You can have different fonts in your label or labels or even use checkboxes to get a return value instead of commandbuttons.  The possibilites are endless.

Posted in Uncategorized

6 thoughts on “Custom Message Boxes

  1. Since the user usually clicks the close box to cancel the form, I usually run the Cancel button code from the close button:

    Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)

    If CloseMode = vbFormControlMenu Then
    Cancel = True
    End If

    End Sub

  2. After about 20 hours of punding my head against the wall, I have just about given up on how to extract the data acquired in an input box. The code I used is pretty basic….
    bQuestion1Text = InputBox(“Please provide brief explanation for failure”, “Question 1 Failure Reason”)

    Now, how can I get that data to paste into a cell on the spreadsheet?

  3. Dan –

    Assuming your variable bQuestion1Text is the correct type, replace the last three lines with this:

    Worksheets(“AgentReview”).Range(“A1?).Value = bQuestion1Text

    If it’s not inserting into the active workbook, prefix the above line with a reference to the appropriate workbook:

    Workbooks(“MyBook.xls”).Worksheets(“AgentReview”).Range(“A1?).Value = bQuestion1Text

    You’ll notice that I didn’t select or activate any of the objects in order to insert the value into the desired cell. For 99% of VBA actions, selecting and activating are unnecessary, and in fact, tend to slow down execution.

  4. Thanks for the input. (no pun intended) Is there a way to get a particular cell value to run the macro in question. I have found many references to changing cells running them, but I need the macro to activate the input box when a value of 0 is entered into a cell by a spinner.

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.