Personal website of Gregory K. Maxey, Commander USN (Retired)
The information, illustrations and code contained in my "Microsoft Word Tips" are provided free and without risk or obligation.
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.
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:
Note: If you are unfamiliar with VBA and the VB Editor see my: Install/Employ VBA Procedures
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.
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.
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
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.
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.
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.
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.