The Anchorage

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

• Quarterdeck
• What's New
• Microsoft Word Tips
• Consulting
• Chain Locker
• Lucky Bag
• Contact
• Search

Do you have ad-blocking software enabled? While I respect your right to do so, your donations and the minimal advertisements on this site help to defray internet and other costs of providing this content. Please consider excluding this website from blocking or turning off the blocker while browsing this site.

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.

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):

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

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:

Create Basic Userform

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

1. Open the VBA Editor (press ALT+F11):

• If not already visible, display the Project Explorer (press CTRL+r) and Properties Window (press F4).
• In the Project Explorer, select your project (template or document) and use the Insert>Userform menu command to create a new blank Userform
• Display the control toolbox. In this exercise, you will be using the Label and CommandButton controls.

2. In the Properties Window, change the Userform name to something meaningful. Most users prefer some type of prefix, so let's call it frmCustomMsgbox.

Note: 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.

3. Grab and drag the edges of the Userform to the required size and shape.
4. 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.

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

Note: 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.

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

8. Use the various form and control properties to further customize the colors, font sizes, and layout of the Userform to suit your needs.
9. Now let's fill in the necessary Userform code. Click View>Code (or press F7) to open the code pane associated with the Userform.
10. 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.

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

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

12. Finally alter the original macro to call and use the Userform data instead of the standard message box.

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.

• Dim myForm As frmCustomMsgbox At this point, myForm is "Nothing." However, in its nothingness it is still a declared, empty, unloaded, and undefined object variable of the type "frmCustomMsgbox"
• Set myForm = New frmCustomMsgbox This creates the actual object and loads myForm with a complete working instance of frmCustomMsgbox. However, the object is not displayed; it is just defined and loaded.
• myForm.Caption = oWord & "starts with ""z""" As the form is now loaded, you can alter its properties at will. In this line, the calling macro provides the text for the .Caption property of the Userform.
• myForm.Show This puts the form on the screen in all its glory. The form dialog box is the active window. If the form is modal (default), some user action (e.g., clicking a button on the form) is required to hide or destroy the form before any more code in the calling macro will be executed
• Me.Tag = "X" The Userform object includes a built-in string property ".Tag" to which you can assign a value. When the user clicks a Userform command button, the _Click event associated with that button is executed. In the _Click function, a value is assigned to the .Tag property.
• Me.Hide In the _Click function, the .Hide method removes the Userform from the screen.
• myForm.Tag   When the _Click function completes, control is returned to the calling macro. The value assigned to the Userform .Tag property is used in the logic process
• Unload myForm This performs a pure technical function that triggers the Userform _QueryClose and _Terminate events. The _QueryClose event provides data that assess how a Userform was actually closed. The _Terminate event "kills" the Userform and releases memory previously allocated to the Userform to the pool of available memory.
• Set myForm = Nothing Can be considered redundant as it is also intended to kill the Userform and releases memory previously allocated to the Userform to the pool of available memory. Some people frown on using the Unload statement, but without it the _QueryClose event isn't triggered and as a matter of personal choice I always use both statements.

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.

This page was last updated on March 09, 2021

Website Privacy Statement

Back to top     Back to Word Tips