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()
    Me.Hide
    mlReturn = CLng(Me.cmd1.Tag)
End Sub

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

Private Sub cmd3_Click()
    Me.Hide
    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
   
    UMsgBox.Show
   
    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.