How To Add A Dialog Box In Excel

In This Chapter

Finding out when to use custom dialog boxes Understanding UserForm objects Displaying a custom dialog box
Creating a custom dialog box that works with a useful macro custom dialog box is useful if your VBA macro needs to get information from a user. For instance, your macro may have some options that tin exist specified in a custom dialog box. If simply a few pieces of information are required (for example, a Yes/No reply or a text string), the techniques I describe in Chapter xv may do the job. Only if y'all need to obtain more information, you lot must create a custom dialog box. In this chapter, you discover out what essential skills yous need to create and work with custom dialog boxes.

Knowing When to Use a Custom Dialog Box (Also Known equally UserForm)

This section describes a state of affairs in which a custom dialog box is useful. The following macro changes the text in each cell in the selection to uppercase letters. It does this past using the VBA congenital-in UCase function.
tmp33-92
You tin can make this macro even more useful. For example, it would be overnice if the macro could likewise change the text in the cells to either lowercase or proper instance (capitalizing the get-go letter in each word). One approach is to create two additional macros (ane for lowercase and one for proper example). Another approach is to alter the macro to handle the other options. Regardless of the approach, you need some method of asking the user which type of change to make to the cells.
The solution is to display a dialog box like the i shown in Effigy 16-i. You lot create this dialog box on a UserForm in the VBE and display information technology using a VBA macro. In the next section, I provide footstep-by-step instructions for creating this dialog box. Just earlier I get into that, I set the stage with some introductory cloth.
You can get information from the user by displaying a custom dialog box.
Figure xvi-one:
You lot tin can go information from the user by displaying a custom dialog box.
In Excel, the official proper name for a custom dialog box is a UserForm. But a UserForm is really an object that contains what'southward commonly known as a dialog box. This distinction isn't important, so I tend to use these terms interchangeably.

Creating Custom Dialog Boxes: An Overview

To create a custom dialog box, you normally have the following general steps:
1. Make up one's mind how the dialog box volition be used and where information technology will be displayed in your VBA macro.
2. Printing Alt+F11 to actuate the VBE and insert a new UserForm object.
A UserForm object holds a single custom dialog box.
iii. Add controls to the UserForm.
Controls include items such as text boxes, buttons, cheque boxes, and list boxes.
4. Use the Properties window to alter the backdrop for the controls or for the UserForm itself.
5. Write effect-handler procedures for the controls (for case, a macro that executes when the user clicks a button in the dialog box).
These procedures are stored in the Code window for the UserForm object.
vi. Write a procedure (stored in a VBA module) that displays the dialog box to the user.
Don't worry if some of these steps seem foreign. I provide more details in the post-obit sections, along with step-by-step instructions for creating a custom dialog box.

Working with UserForms

Each custom dialog box that you lot create is stored in its own UserForm object — one dialog box per UserForm. You create and access these UserForms in the Visual Basic Editor.

Inserting a new UserForm

Insert a UserForm object with the following steps:
i. Actuate the VBE by pressing Alt+F11.
2. Select the workbook in the Project window.
3. Cull InsertOUserForm.
The VBE inserts a new UserForm object, which contains an empty dialog box.
Figure 16-2 shows a UserForm — an empty dialog box.

Figure 16-2:
A new UserForm object.

Adding controls to a UserForm

When you activate a UserForm, the VBE displays the Toolbox in a floating window, equally shown in Figure 16-3. You use the tools in the Toolbox to add controls to your custom dialog box. Just click the desired command in the Toolbox and drag it into the dialog box to create the control. After y'all add a control, you lot tin move and resize it using standard techniques.

Effigy 16-3:
You use the tools in the Toolbox to add together controls to a UserForm.
Tabular array 16-one indicates the various tools, as well as their capabilities. To determine which tool is which, hover your mouse push button over the control and read the small pop-upwardly clarification.

Table 16-1	Toolbox Controls
Command	What It Does
Characterization	Stores text
TextBox	Allows the user to enter text
ComboBox	A driblet-down list
ListBox	A list of items
CheckBox	Useful for on/off or yes/no options
OptionButton	Used in groups, allows the user to select one of sev-
	eral options
ToggleButton	A button that is either on or off
Frame	Contains other controls
CommandButton	A clickable push button
TabStrip	Displays tabs
MultiPage	Tabbed container for other objects
ScrollBar	Draggable bar
SpinButton	Clickable push often used for changing a value
Image	Contains an prototype
RefEdit	Allows the user to select a range

Changing properties for a UserForm control

Every control y'all add together to a UserForm has a number of properties that decide how the dialog box looks or behaves. Y'all can change these properties with the Properties window, shown in Effigy 16-4. The Properties window appears when you press F4, and the backdrop shown in this window depend on what is selected. If you select a dissimilar control, the backdrop change to those appropriate for that control. To hide the Properties window, click the close push in its title bar.

Figure xvi-4:
Use the Properties windows to change the properties of UserForm controls.

Properties for controls include the post-obit:

Name
Width
Height
Value
Caption
Each control has its own prepare of properties (although many controls accept many mutual properties). The next affiliate tells you everything you demand to know about working with dialog box controls.
If you select the UserForm itself (not a command on the UserForm), you can utilize the Properties window to suit UserForm properties.

Viewing the UserForm Code window

Every UserForm object has a Code window that holds the VBA code (the effect-handler procedures) executed when the user works with the dialog box. To view the Lawmaking window, press F7. The Lawmaking window is empty until y'all add some procedures. Printing Shift+F7 to render to the dialog box.
Some other way to switch between the Code window and the UserForm display: Employ the View Code and View Object buttons in the Project window's title bar. You notice out more about the Code window in Chapter 17.

Displaying a custom dialog box

You tin display a custom dialog box by using the UserForm's Show method in a VBA procedure.
The macro that displays the dialog box must be in a VBA module — not in the Code window for the UserForm.

The following procedure displays the dialog box named UserForm1:

tmp49-2_thumb
When Excel displays the dialog box, the macro halts until the user closes the dialog box. Then VBA executes any remaining statements in the procedure. Almost of the time, you lot won't have whatever more code in the procedure. As y'all after come across, you can put your macro code in the Lawmaking window for the UserForm.

Using information from a custom dialog box

The VBE provides a name for each control you add to a UserForm. The control's name corresponds to its Name holding. Use this name to refer to a particular control in your code. For case, if you add together a CheckBox control to a UserForm named UserForml, the CheckBox command is named CheckBoxl by default. The following statement makes this control appear with a check mark:

Your VBA code can as well bank check various properties of the controls and take advisable actions. The following statement executes a macro named PrintReport if the cheque box (named CheckBoxl) is checked:

I hash out this topic in detail in the next chapter.

A Custom Dialog Box Instance

This department'southward UserForm example is an enhanced version of the ChangeCase macro from the beginning of the affiliate. Recall that the original version of this macro changes the text in the selected cells to upper-case letter. This modified version uses a custom dialog box to ask the user which blazon of modify to make: upper-case letter, lowercase, or proper case.
This dialog box needs to obtain one slice of information from the user: the type of change to make to the text. Because the user has three choices, your best bet is a custom dialog box with iii OptionButton controls. The dialog box besides needs two more buttons: an OK push button and a Cancel button. Clicking the OK push runs the code that does the work. Clicking the Cancel button causes the macro to halt without doing anything.
This workbook is available at the topic's Web site. Nevertheless, you get more out of this exercise if yous follow the steps provided here and create it yourself.

Creating the custom dialog box

These steps create the custom dialog box. Beginning with an empty workbook.
one. Press Alt+F11 to activate the VBE.
2. If multiple projects are in the Projection window, select the projection that corresponds to the workbook you're using.
three. Choose InsertOUserForm.
The VBE inserts a new UserForm object with an empty dialog box.
4. Printing F4 to display the Properties window.
5. In the Properties window, change the dialog box's Caption property
to Modify Case.
half-dozen. The dialog box is a bit too large, so you may want to click it and apply the handles to brand it smaller.
Step 6 can also be done later on you position all the controls in the dialog box.

Adding the CommandButtons

Gear up to add together ii CommandButtons — OK and Abolish — to the dialog box? Follow along:
1. Make sure the toolbox is displayed. If it isn't, cull ViewOToolbox.
ii. If the Properties window isn't visible, printing F4 to display information technology.
3. In the toolbox, drag a CommandButton into the dialog box to create a button.
As you lot run into in the Backdrop box, the button has a default proper noun and explanation: CommandButtonl.
4. Make sure the CommandButton is selected. Then activate the Backdrop window and change the following properties:
Property Alter To
Name OKButton
Caption OK
Default True
5. Add a second CommandButton object to the UserForm and change the post-obit properties:
Belongings Modify To
Proper noun CancelButton
Caption Cancel
Cancel True
6. Adjust the size and position of the controls so your dialog box looks something similar Effigy xvi-v.
The UserForm with two Command-Button controls.
Figure 16-5:
The UserForm with two Control-Button controls.

Adding the OptionButtons

In this section, y'all add three OptionButtons to the dialog box. Before adding the OptionButtons, yous add together a Frame object that contains the OptionButtons. The Frame isn't necessary, just information technology makes the dialog box look better.
i. In the toolbox, click the Frame tool and drag in the dialog box.
This creates a frame to hold the options buttons.
2. Use the Properties window to modify the frame'due south caption to Options.
iii. In the Toolbox, click the OptionButton tool and drag in the dialog box (inside the Frame).
This creates an OptionButton control.
4. Select the OptionButton and use the Properties window to change the following properties:
Property Change To
Name OptionUpper
Caption Upper Case
Accelerator U
Value Truthful
Setting the Value holding to Truthful makes this OptionButton the default.
5. Add another OptionButton and utilize the Properties window to alter the following properties:
Property Change To
Name OptionLower
Caption Lower Instance
Accelerator L
6. Add together a tertiary OptionButton and utilise the Properties window to modify the following properties:
Property Change To
Name OptionProper
Caption Proper Case
Accelerator P
7. Adjust the size and position of the OptionButtons, Frame, and dialog box.
Your UserForm should wait something like Figure 16-6.
The Accelerator property determines which letter in the caption is underlined. For example, y'all tin can select the Lower Case selection by pressing Alt+L considering the 50 is underlined.

Figure 16-6:
This is the UserForm after adding 3 Option Button controls inside a Frame control.
You may wonder why the OptionButtons have accelerator keys simply the CommandButtons go without. Generally, OK and Cancel buttons never have accelerator keys because they can be accessed from the keyboard. Pressing Enter is equivalent to clicking OK because the control'due south Default belongings is True. Pressing Esc is equivalent to clicking Cancel, because the control's Abolish property is True.

Adding event-handler procedures

Here'southward how to add an issue-handler procedure for the Cancel and OK buttons:
1. Double-click the Cancel button.
VBE activates the Code window for the UserForm and inserts an empty procedure:

The procedure named CancelButton_Click is executed when the Cancel button is clicked, merely only when the dialog box is displayed. In other words, clicking the Cancel button when you're designing the dialog box won't execute the procedure. Considering the Cancel button's Cancel property is set to True, pressing Esc besides triggers the CancelButton_Click procedure.
2. Insert the post-obit statement inside the procedure (before the End Sub statement):

This statement merely closes the UserForm when the Cancel push is clicked.
iii. Press Shift+F7 to return to the UserForm.
iv. Double-click the OK button.
VBE activates the code window for the UserForm and inserts an empty Sub process called

Clicking OK executes this process. Because this button has its Default property prepare to True, pressing Enter also executes the OKButton_Click process.
five. Enter the following code inside the process:
tmp49-10_thumb
The preceding code is an enhanced version of the original ChangeCase macro that I presented at the offset of the affiliate. The macro consists of three separate blocks of code. This code uses 3 If-Then structures; each i has a For Each loop. Only 1 block is executed, co-ordinate to which OptionButton the user selects. The final statement unloads (closes) the dialog box afterwards the work is finished.
Notice that VBA has a UCase function and an LCase part, but not a office to convert text to proper instance. Therefore, I use Excel'due south PROPER worksheet function (preceded by Application.WorksheetFunction) to do the actual
conversion. Another option is to utilise the VBA StrConv part. (See the Aid system for details.) The StrConv function is not available in all Excel versions, and then I used the PROPER worksheet role instead.

Creating a macro to display the dialog box

The only thing missing is a way to brandish the dialog box. Follow these steps to make the procedure that makes the dialog box announced:
one. In the VBE window, choose Insert Module.
The VBE adds an empty VBA module (named Modulel) to the project.
2. Enter the following code:
tmp49-11_thumb
This process is simple. It checks to brand sure that a range is selected. If non, the macro ends with no activity. If a range is selected, the dialog box is displayed (using the Show method). The user then interacts with the dialog box and the code stored in the UserForm's Code pane is executed.

Making the macro available

At this signal, everything should be working properly. But you withal need an easy way to execute the macro. Assign a shortcut key (Ctrl+Shift+C) that executes the ChangeCase macro:
one. Activate the Excel window via Alt+F11.
ii. Choose Tools Macro Macros or press Alt+F8.
3. In the Macros dialog box, select the ChangeCase macro.
4. Click the Options push button.
Excel displays its Macro Options dialog box.
5. Enter an uppercase C for the Shortcut key.
See Effigy xvi-7.
6. Enter a description of the macro in the Description field.
vii. Click OK.
8. Click Abolish when yous return to the Macro dialog box.

Effigy sixteen-7:
Assign a shortcut key to execute the Change-Case macro.
Later on y'all perform this functioning, pressing Ctrl+Shift+C executes the ChangeCase macro, which displays the custom dialog box if a range is selected.

Testing the macro

Finally, yous need to test the macro and dialog box to make sure they work properly:
1. Activate a worksheet (whatever worksheet in any workbook).
2. Select some cells that contain text.
3. Press Ctrl+Shift+C.
The custom dialog box appears. Effigy xvi-viii shows how it should expect.
4. Make your pick and click OK.
If y'all did everything correctly, the macro makes the specified change to the text in the selected cells.
Effigy 16-9 shows the worksheet after converting the text to uppercase.
As long equally the workbook is open up, you tin execute the macro from any other workbook. If you lot close the workbook that contains your macro, Ctrl+Shift+C no longer has any function.
If the macro doesn't work properly, double-check the preceding steps to locate and right the error. Don't exist alarmed; debugging is a normal part of developing macros. Equally a last resort, download the completed workbook from this topic'southward Web site.

Effigy 16-8:
The custom dialog box is in action.

Figure sixteen-9:
The text has been converted to uppercase.