Resting Anchor

The Anchorage

Personal website of Gregory K. Maxey, Commander USN (Retired)

Custom VBA\Userform Message Box
(A Microsoft Word Help & Tip page by Gregory K. Maxey)

DISCLAIMER/TERMS OF USE

The information, illustrations and code contained in my "Microsoft Word Tips" are provided free and without risk or obligation.

Click to acces PayPal Verification Service Click to acces PayPal Verification Service

However, the work is mine. If you use it for commercial purposes or benefit from my efforts through income earned or time saved then a donation, however small, will help to ensure the continued availability of this resource.

If you would like to donate, please use the appropriate donate button to access PayPal. Thank you!


This Microsoft Word Help & Microsoft Word Tips pages is provided as my introduction and basic primer on Userforms.  Here I will show you how to create a simple custom UserFrom and have it stand in for aVBA  msgbox function. 

VBA message and input box functions are very limited in their graphical presentation and opitons. A custom UseForm in place of a standard VBA message/input boxes can make your projects easier t to use and spruce up the presentation.

cust vba msg 1

Grey, drab, and like a U.S. Navy warship, generally unappealing, a VBA message box interface buttons include vbYes/No/Cancel (as shown), vbYes/No, vbOkOnly, vbOk/Cancel, vbAbortRetryIgnore and vbRetryCancel.

For an Input Box the interface is the user input field,  Ok and Cancel. That's it.

You can usually work around these limitations through "creative" phrasing of the message or input box text, but there isn't much you can do about the actual buttons themselves.

With a VBA Userform module you can overcome these limitations and significantly expand user options and presentation in your projects.

Consider this, admittedly not very practical, example where the user is requested to provide direction to a macro processing all words in a document that starts with the letter "z" (upper or lower case):

cust vba msg 2

The message box is presented via standard VBA code (shown in blue) in the processing macro

VBA Script:
Option Explicit
Sub DeleteCertainWords()
Dim oWord As Range
Dim bAutoDelete As Boolean
  bAutoDelete = False
  For Each oWord In ActiveDocument.Range.Words
    If UCase(oWord.Characters.First) = "Z" Then
      oWord.Select
      If bAutoDelete Then
        oWord.Delete
      Else
        Select Case MsgBox("Delete this word: Yes/No?" _
            & vbCr & vbCr & "Press ""Cancel"" to suppress this" _
            & " popup." & vbCr & "and AutoDelete all words that" _
            & " start with ""z"".", _
            vbYesNoCancel + vbQuestion, oWord & _
            " starts with 'z'.")
          Case vbYes
            oWord.Delete
          Case vbNo
            'Do nothing.
          Case 3
            oWord.Delete
            bAutoDelete = True
        End Select
      End If
    End If
  Next oWord
End Sub

The author of the code clearly understands that the user may tire of processing each word one by one and wants to offer the ability to get on with it and auto process any remaining words that start with "z."

As you can see "Yes, No, Cancel" or the other combinations available don't really lend themselves to the desired options. "Cancel" should mean just that. With a standard VBA message box, you have to cheat and use descriptive explanatory message text to make alternative options even workable.

With a Userform you can have the look, layout, and options tailored to your specific need:

cust vba msg 3

Create Basic Userform

Site Note IconNote: If you are unfamiliar with VBA and the VB Editor see my:  Install/Employ VBA Procedures

  1. Open the VBA Editor (press ALT+F11):
cust vba msg 4
  1. In the Properties Window, change the Userform name to something meaningful. Most users prefer some type of prefix, so let's call it frmCustomMsgbox.

Site Note IconNote: For a static Userform, you would also change the Caption property to define the text to appear in the Userform title bar. In this exercise, the title text will be dynamic and dependent on the data passed from the calling macro.

cust vba msg 5
  1. Grab and drag the edges of the Userform to the required size and shape.
  2. Use the "Label" control tool to create the message text. Click the control in the toolbox and drag an appropriate sized box on the surface of the Userform. Define the label text in the Properties Window using the Caption property.
cust vba msg 6
  1. Use the "CommandButton" control to create the user option buttons. Click the control in the toolbox and then click in the Userform to insert a Command Button. Repeat to insert three more buttons.

Site Note IconNote: You can double-click the control in the toolbox to make it "sticky", and then simply click four times on the Userform to insert all four controls. Double-click again in empty space in the toolbox to release the "sticky" item.

cust vba msg 7
  1. Grab and drag the individual command buttons to align, size and position them, or select multiple buttons (CTRL+ mouse click) and use the mouse right click "Align" or "Make Same Size" menu items to alter multiple controls.
  2. Click the first button and in the Properties Window change the name property to something like cmdDeleteYes. Change the caption property to "Yes." Repeat as appropriate to define the remaining three command button controls. For the "Cancel" command button, set its "Cancel" property to "True" to link it to the keyboard Esc key.
cust vba msg 8
  1. Use the various form and control properties to further customize the colors, font sizes, and layout of the Userform to suit your needs.
  2. Now let's fill in the necessary Userform code. Click View>Code (or press F7) to open the code pane associated with the Userform.
  3. Click the dropdown that says "(General)" and select one of the button names. This creates a function that gets called when that button is clicked (note the "_Click" in the function's name). Repeat for the other three buttons.
cust vba msg 9
  1. Paste the code below in the userform code pane. The code in each of these click event procedures sets a unique value to the .Tag property of the Userform, hides the Userform from view, and returns control to the macro that called e Userform. We are going to use the value of the Userform .Tag property in the calling macro. For detailed discussion and more examples of passing data to and from Userforms and calling macros, see: Pass Data To/From Userform
VBA Script:
Option Explicit
Private Sub cmdAutoDelete_Click()
  Me.Tag = 3
  Me.Hide
End Sub
Private Sub cmdCancel_Click()
  Me.Tag = 0
  Me.Hide
End Sub
Private Sub cmdDeleteNo_Click()
  Me.Tag = 1
  Me.Hide
End Sub
Private Sub cmdDeleteYes_Click()
  Me.Tag = 2
  Me.Hide
End Sub
Private Sub Userform_QueryClose(Cancel As Integer, CloseMode As Integer)
  If CloseMode = vbFormControlMenu Then Cancel = True
End Sub
  1. Finally alter the original macro to call and use the Userform data instead of the standard message box.
VBA Script:
Sub DeleteCertainWords()
Dim oWord As Range
Dim bAutoDelete As Boolean
Dim myForm As frmCustomMsgbox
  Set myForm = New frmCustomMsgbox
  bAutoDelete = False
  For Each oWord In ActiveDocument.Range.Words
    If UCase(oWord.Characters.First) = "Z" Then
      oWord.Select
      If bAutoDelete Then
        oWord.Delete
      Else
        myForm.Caption = oWord & " starts with ""Z"""
        myForm.Show
        Select Case myForm.Tag
          Case 0
            GoTo lbl_Exit
          Case 1
            'Do nothing.
          Case 2
            oWord.Delete
          Case 3
            oWord.Delete
            bAutoDelete = True
        End Select
      End If
    End If
  Next oWord
lbl_Exit:
Unload myForm
Set myForm = Nothing
Exit Sub
End Sub

Now simply run your procedure again an take advantage of the enhance look and functioanilty provided by the Userform.

Discussion

The first 11 steps create the definition of frmCustomMsgbox, which is a "pattern" for a Userform. Strictly speaking a Userform is a user-defined data type.

In step 12, the calling macro is provided with all the necessary code to create, load, show, utilize data, unload and ultimately kill the instance of the Userform.

Site Note icon See: Installing Macros for instructions on how to set up and use the macros provided in this Microsoft Word Help & Microsoft Word Tips page.

That's it! I hope you have found this tips page useful and informative.  For more on Userforms see my tutorial: Create and Employ a Userform.

PAYMENTS/DONATIONS

Click to acces PayPal Verification Service Click to acces PayPal Verification Service

Do you want to make a payment for consulting work or donate to help support this site?

PayPal is a safe, easy way to pay online.

Use the appropriate currency "Donate" button to make a payment or donation.


Search my site or the web using Google Search Engine

Google Search Logo