Custom VBA Message Box

The information in this website is provided without risk or obligation and free of charge. However, if you have benefitted from my efforts here and would like to make a contribution to help me continue and maintain this work then any donation will be greatly appreciated. Please click the adjacent button to access PayPal. Thank you.

The option buttons available to VBA message and input boxes are very limited. This Microsoft Help & Tips page shows you methods for using a UserForm in place of a standard VBA message box.

For a Message Box there are vbYesNoCancel (as shown above), vbYesNo, vbOkOnly, vbOkCancel, vbAbortRetryIgnore and vbRetryCancel. For an Input Box there is 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.

This Tips Page will show you how to create and use a simple Word UserForm. The objective is to create a UserForm that will expand user option buttons and overcome the limitations of a standard VBA message box or input box.

Note: These methods are tested and demonstrated in Word only. There is no UserForm in Access and I am not sure of the other Office Applications.

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 shown above is generated by the processing macro:

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:

First create the UserForm

1. Open the VBA Editor (press ALT+F11). If not already visible, display the Project Explorer (press CTRL+r) and the 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 and 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 lets 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.

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", then simply click four times on the UserForm to insert all four controls.)

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. Type in code as show below. 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 the 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

12. Finally let's alter the original macro provided above to call and use the UserForm data instead of the standard message box:

Technical details: 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 (i.e., 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.

Within the UserForm object there is a built-in string property ".Tag" to which you can assign a value.

When the user clicks a button, the _Click function belonging to that button gets called. In the _Click function, a value is assigned to the .Tag property and then the .Hide method is called.

The .Hide method removes the UserForm from the screen and returns control to the calling macro at the line after .Show. That line grabs the value of the UserForm's .Tag property and uses it 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.

And finally

- Set myForm = Nothing. Could be considered redundant as its function also kills 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.

Looking for something else?