Knowing When to Use a Custom Dialog Box Also Known as User Form

This section describes a situation 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 by using the VBA built-in UCase function.

Sub ChangeCase()

Dim cell As Range

If TypeName(Selection) = "Range"

Then

For Each cell In Selection

cell.Value = UCase(cell.

Value)

Next cell

End If

End Sub

You can make this macro even more useful. For example, it would be nice if the macro could also change the text in the cells to either lowercase or proper case (capitalizing the first letter in each word). One approach is to create two additional macros (one for lowercase and one for proper case). Another approach is to modify 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 one shown in Figure 16-1. You create this dialog box on a UserForm in the VBE and display it using a VBA macro. In the next section, I provide step-by-step instructions for creating this dialog box. But before I get into that, I set the stage with some introductory material.

Figure 16-1:

You can get information from the user by displaying a custom dialog box.

^ ' chantie case.xls

-QtâlS

A

B

c

D

E

F 1

A

1

January

2

r^KUai'/:

„3

Ivlarsfi'

4

April

5

*

July_

August

Wûï&M

Chang

Case

m

S, 10

(* Upper Case

1j|

Mcn/K.'-ivLiet

Cancel

12

December

r

Lower Case Proper Case

V

14

|! 0K j|

15

lb

IT

13

h < > H [\Shsetl / 1<

H

You can get information from the user by displaying a custom dialog box.

In Excel, the official name for a custom dialog box is a UserForm. But a UserForm is really an object that contains what's 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 usually take the following general steps:

1. Determine how the dialog box will be used and where it will be displayed in your VBA macro.

2. Press Alt+F11 to activate the VBE and insert a new UserForm object.

A UserForm object holds a single custom dialog box.

3. Add controls to the UserForm.

Controls include items such as text boxes, buttons, check boxes, and list boxes.

4. Use the Properties window to modify the properties for the controls or for the UserForm itself.

5. Write event-handler procedures for the controls (for example, 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.

6. 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 following sections, along with step-by-step instructions for creating a custom dialog box.

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

Insert a UserForm object with the following steps:

1. Activate the VBE by pressing Alt+F11.

2. Select the workbook in the Project window.

3. Choose InsertOUserForm.

The VBE inserts a new UserForm object, which contains an empty dialog box.

Was this article helpful?

0 0

Post a comment