Capture Input From A Custom Dialog

You create code to monitor events caused by controls to determine when specific code should execute. Each control has its own specific events that you can capture, and the Visual Basic Editor keeps track of those for you. You can quickly create an event procedure on the code window by selecting the appropriate control name in the Object list box and then selecting the corresponding event from the Procedure list box. When you select an event, the Visual Basic Editor creates a procedure with the name of the control followed by the event name.

All control values on a UserForm are only active as long as you have the dialog box open. If you close the dialog box prior to saving user input values, you lose the user input. To avoid any potential problems with lost data, consider saving user responses to global variables that can pass into other procedures. For example, you typically call a UserForm from another procedure to capture user responses and then pass the values back to the main procedure.

You must declare public variables at the top of your module, before any procedure code, using the Public statement. Doing so enables you to declare variables that all procedures within a project can access. See Chapter 3 for more information on declaring variables.

You can also use the With statement to shorten the code required to set properties for an object. See Chapter 4 for more information on using the With statement.

CAPTURE INPUT FROM A CUSTOM DIALOG BOX (CONTINUED)

CAPTURE INPUT FROM A CUSTOM DIALOG BOX (CONTINUED)

Note: See Chapter 2 for information on creating modules.

I Type Public UserSelection As String, replacing UserSelection with the name of the global variable.

Create a new subroutine.

E Type With

UserForm1.ListBox1, replacing UserForml and ListBoxl with the names of the UserForm and ListBox controls, respectively.

Q Type .AddItem "January", replacing "January" with the value to add to the ListBox control.

£ Repeat step 5 for each item to place in the control.

-E Type End With.

CUSTOMIZING DIALOG BOXES, MENUS, AND TOOLBARS

CONTROL EVENTS

OCCURRENCE

BeforeDragOver

User is drag-and-dropping data onto a control.

BeforeUpdate

Before data on a control is changed.

Change

When the Value property of the control changes.

Click

When user clicks the control with the mouse button.

DblClick

When the user clicks twice with the mouse on the control.

Enter

Before a control receives focus.

KeyDown

When the user presses a key.

MouseDown

When the user presses the mouse button.

E Switch to Excel and run the macro.

■ The dialog box displays the list of values specified by the subroutine.

U Type SampleDialogBox, replacing SampleDialogBox with the name of the UserForm.

Type additional VBA code to process the user selection value returned by the global variable.

E Switch to Excel and run the macro.

Note: See Chapter 1 to run a macro.

■ The dialog box displays the list of values specified by the subroutine.

0 0

Post a comment