VBA Express : Excel - Customise a MsgBox

Excel

Customise a MsgBox - Use a UserForm!

Ease of Use

Easy

Version tested with

2016

Submitted by:

paulked

Description:

Using a UserForm instead of the standard Msgbox has many advantages!

Discussion:

I recently had an application that needed to be bilingual. It isn't possible to change the text on the Msgbox buttons so I used this UserForm instead. Not only can you customise the buttons, but you can add your own images (four in the included example), change the colours, place it where you want... it is limited only by your imagination!

Code:

instructions for use

			modMsg code: 
 
Sub Msg(Txt As String, Optional But1 As String, Optional But2 As String, _ 
    Optional But3 As String, Optional But4 As String, Optional Title As String, _ 
    Optional Snd As Boolean, Optional Image As Long, Optional Cncl As Boolean) 
    Dim i As Long 
    If Snd Then Beep 
    If But1 = "" And But2 = "" And But3 = "" And But4 = "" Then But4 = "OK" 
    Load frmMsg 
    With frmMsg 
        For i = 1 To 4 
            If Image = i Then 
                .Controls("Image" & i).Visible = True 
            Else 
                .Controls("Image" & i).Visible = False 
            End If 
        Next 
        .lb1 = Txt 
        .bt1.Caption = But1 
        .bt2.Caption = But2 
        .bt3.Caption = But3 
        .bt4.Caption = But4 
        .Caption = Title 
        .lbCncl = Cncl 
        .Show 
    End With 
End Sub 
 
frmMsg UserForm code: 
 
Private Cncl As Boolean 
 
Private Sub bt1_Click() 
    Tag = 1 
    Me.Hide 
End Sub 
 
Private Sub bt2_Click() 
    Tag = 2 
    Me.Hide 
End Sub 
 
Private Sub bt3_Click() 
    Tag = 3 
    Me.Hide 
End Sub 
 
Private Sub bt4_Click() 
    Tag = 4 
    Me.Hide 
End Sub 
 
Private Sub UserForm_Activate() 
    Dim i&, j& 
    If lbCncl = "True" Then 
        Cncl = True 
    Else 
        Cncl = False 
    End If 
    lb1.AutoSize = True 
    lb1.Width = 306 
    lb1.Top = 6 
    lb1.Left = 6 
    For i = 1 To 3 
        If Controls("Image" & i).Visible = True Then 
            If lb1.Height < 70 Then j = 70 - lb1.Height 
            lb1.Width = 236 
        End If 
    Next 
    Me.Height = lb1.Height + 80 + j 
    For i = 4 To 1 Step -1 
        If Controls("bt" & i).Caption = "" Then 
            Controls("bt" & i).Visible = False 
            Controls("bt" & i).Top = lb1.Height + 15 + j 
        Else 
            Controls("bt" & i).Visible = True 
            Controls("bt" & i).SetFocus 
            Controls("bt" & i).Top = lb1.Height + 15 + j 
        End If 
    Next 
End Sub 
 
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer) 
    Cancel = Cncl 
End Sub

How to use:

Open the file included below.
Copy the frmMsg userform to your project.
Copy the modMsg code module to your project.
Modify it to suit your needs!

Test the code:

Instead of
Msgbox "This is a simple 'OK' only message box"
Use
Msg "This is a simple 'OK' only message box"
For more features download the file!

Sample File:

frmMsg with examples.zip 246.13KB

Approved by Jacob Hilderbrand

This entry has been viewed 46 times.