Working with Custom User Forms

Working with Custom User Forms

Excel developers have always had the ability to create custom dialog boxes for their applications. Beginning with Excel 97, things changed substantially — UserForms replaced the clunky old dialog sheets. UserForms are much easier to work with, and they offer many additional capabilities. Even though UserForms haven’t been upgraded over the years, you’ll find that this feature works well and is very flexible.

A custom dialog box is created on a UserForm, and you access UserForms in the Visual Basic Editor (VBE).

Following is the typical sequence that you’ll follow when you create a UserForm:

1.                   Insert a new UserForm into your workbook’s VB Project.

2.                   Add controls to the UserForm.

3.                   Adjust some of the properties of the controls that you added.

4.                   Write event-handler procedures for the controls. (These procedures, which are located in the code window for the UserForm, are executed when various events (such as a button click) occur).

5.                   Write a procedure that will display the UserForm.(This procedure will be located in a VBA module (not in the code module for the UserForm)).

6.                   Add a way to make it easy for the user to execute the procedure you created in Step 5.(You can add a button to a worksheet, a Ribbon command, and so on).

Inserting a New UserForm

To insert a new UserForm, activate the VBE (press Alt+F11), select your workbook’s project from the Project window, and then choose Insert➜UserForm. UserForms have default names like UserForm1, UserForm2, and so on. You can change the name of a UserForm to make it easier to identify. Select the form and use the Properties window to change the Name property. (Press F4 if the Properties window isn’t displayed.) Figure 13-1 shows the Properties window when an empty UserForm is selected. A workbook can have any number of UserForms, and each UserForm holds a single custom dialog box.

Below is the empty Userform.

To add controls to a UserForm, use the Toolbox. (The VBE doesn’t have menu commands that add controls.) If the Toolbox isn’t displayed, choose View➜Toolbox. Above Figure shows the Toolbox. The Toolbox is a floating window, so you can move it to a convenient location. Use the Toolbox to add controls to a UserForm. Click the Toolbox button that corresponds to the control that you want to add and then click inside the dialog box to create the control (using its default size). Or you can click the control and then drag in the dialog box to specify the dimensions for the control.

When you add a new control, it’s assigned a name that combines the control type with the numeric sequence for that type of control. For example, if you add a CommandButton control to an empty UserForm, it’s named CommandButton1. If you then add a second CommandButton control, it’s named CommandButton2. Renaming all the controls that you’ll be manipulating with your VBA code is a good idea. Doing so lets you refer to meaningful names (such as ProductListBox) rather than generic names (such as ListBox1). To change the name of a control, use the Properties window in the VBE. Just select the object and change the Name property.

Below figure shows the completed UserForm for an example. For best results, start with a new workbook. Then follow these steps:

 

·         Press Alt+F11 to activate the VBE.

·         In the Project window, select the workbook’s project and choose Insert➜UserForm to add an empty UserForm.

·         The UserForm’s Caption property will have its default value: UserForm1.

·         Use the Properties window to change the UserForm’s Caption property to Get Data.(If the Properties window isn’t visible, press F4.). Also change the name of UserForm1 to GetData as shown in above figure.

·         Add some controls and adjust the properties as follows:

Control Name	Property	Value		Control Name	Property	Value
Label1	Accelerator	N		Text Box	Name	TextAdd1
Label1	Caption	Name
Label1	TabIndex	0		Control Name	Property	Value
				Label5	Caption	Address2
Control Name	Property	Value
Text Box	Name	TextName		Control Name	Property	Value
Text Box	TabIndex	1		Text Box	Name	TextAdd2
Control Name	Property	Value		Control Name	Property	Value
Frame1	Caption	Gender		Label6	Caption	Address3
Frame1	TabIndex	2
				Control Name	Property	Value
Control Name	Property	Value		Text Box	Name	TextAdd3
OptionButton1	Accelerator	M
OptionButton1	Name	OptionMale		Control Name	Property	Value
OptionButton1	Caption	Male		Label7	Caption	City
OptionButton1	TabIndex	0
				Control Name	Property	Value
Control Name	Property	Value		Text Box	Name	TextCity
OptionButton2	Accelerator	F
OptionButton2	Name	OptionFemale		Control Name	Property	Value
OptionButton2	Caption	Female		Label8	Caption	State
OptionButton2	TabIndex	1
				Control Name	Property	Value
Control Name	Property	Value		ComboBox	Name	ComboState
OptionButton3	Accelerator	O		ComboBox	Row Source	Sheet1!AA2:AA38
OptionButton3	Name	OptionOther
OptionButton3	Caption	Other		Control Name	Property	Value
OptionButton3	TabIndex	2		Label9	Caption	Pin Code
Control Name	Property	Value		Control Name	Property	Value
Frame2	Caption	Marital Status		Text Box	Name	TextPin
Frame2	TabIndex	3
				Control Name	Property	Value
Control Name	Property	Value		Label9	Caption	Pin Code
OptionButton1	Accelerator	U
OptionButton1	Name	OptionUnmarried		Control Name	Property	Value
OptionButton1	Caption	Unmarried		Text Box	Name	TextPin
OptionButton1	TabIndex	0
				Control Name	Property	Value
Control Name	Property	Value		Label10	Caption	Work Phone
OptionButton2	Accelerator	M
OptionButton2	Name	OptionMarried		Control Name	Property	Value
OptionButton2	Caption	Married		Text Box	Name	TextWorkPh
OptionButton2	TabIndex	1
				Control Name	Property	Value
Control Name	Property	Value		Label11	Caption	Home Phone
OptionButton3	Accelerator	D
OptionButton3	Name	OptionDivorced		Control Name	Property	Value
OptionButton3	Caption	Divorced		Text Box	Name	TextHomePh
OptionButton3	TabIndex	2
				Control Name	Property	Value
Control Name	Property	Value		Label12	Caption	Email Address
Label2	Caption	Date of Birth
				Control Name	Property	Value
Control Name	Property	Value		Text Box	Name	TextEmail
Text Box	Name	TextDOB
				Control Name	Property	Value
Control Name	Property	Value		Command Button1	Accelerator	S
Label3	Caption	Anniversary Date		Command Button1	Name	SaveButton
				Command Button1	Caption	Save
Control Name	Property	Value
Text Box	Name	TextAnniver		Control Name	Property	Value
				Command Button2	Accelerator	E
Control Name	Property	Value		Command Button2	Name	ExitButton
Label4	Caption	Address1		Command Button2	Caption	Exit

Code to display the dialog box

Next, you add an ActiveX CommandButton to the worksheet. This button will execute a procedure that displays the UserForm. Here’s how:

1.       Activate Excel.(Alt+F11 is the shortcut key combination.)

2.       Choose DeveloperàControlsàInsert and click CommandButton from the ActiveX Controls section.

3.       Drag in the worksheet to create the button. If you like, you can change the caption for the worksheet CommandButton. To do so, right-click the button and choose CommandButton ObjectàEdit from the shortcut menu. You can then edit the text that appears on the CommandButton. To change other properties of the object, right-click and choose Properties. Then make the changes in the Properties box.

4.       Double-click the CommandButton. This step activates the VBE. More specifically, the code module for the worksheet will be displayed, with an empty event-handler procedure for the worksheet’s CommandButton.

5.       Enter a single statement in the CommandButton1_Click procedure (see Below Code). This short procedure uses the Show method of an object (UserForm1) to display the UserForm.

Private Sub CommandButton1_Click()

UserForm1.Show

End Sub

Adding event-handler procedures

In this section, I explain how to write the procedures that will handle the events that occur when the UserForm is displayed. To continue the example, do the following:

1.       Press Alt+F11 to activate the VBE.

2.       Make sure that the UserForm is displayed and double-click the CommandButton captioned Exit. This step activates the Code window for the UserForm and inserts an empty procedure named ExitButton_Click. Notice that this procedure consists of the object’s name, an underscore character, and the event that it handles.

3.       Modify the procedure as follows. (This is the event handler for the ExitButton’s Click event.)

Private Sub ExitButton_Click()

Unload GetData ‘As we have change the name of userform1 to GetData

End Sub

This procedure, which is executed when the user clicks the Close button, simply unloads the UserForm.

4.       Press Shift+F7 to redisplay UserForm1\GetData (or click the View Object icon at the top of the Project Explorer window).

5.       Double-click the Save button and enter the following procedure. (This is the event handler for the SaveButton’s Click event.)

Private Sub SaveButton_Click()

Dim NextRow As Long

' Make sure Sheet1 is active

Sheets("Sheet1").Activate

' Determine the next empty row

NextRow = Application.WorksheetFunction.CountA(Range("A:A")) + 1

' Transfer the name

Cells(NextRow, 1) = TextName.Text

If TextName.Text = "" Then

MsgBox "You must enter a name."

TextName.SetFocus

End If

' Transfer the sex

If OptionMale Then Cells(NextRow, 2) = "Male"

If OptionFemale Then Cells(NextRow, 2) = "Female"

If OptionUnknown Then Cells(NextRow, 2) = "Unknown"

' Transfer the Marital Status

If OptionUnMarried Then Cells(NextRow, 4) = "UnMarried"

If OptionMarried Then Cells(NextRow, 4) = "Married"

If OptionDivorced Then Cells(NextRow, 4) = "Divorced"

Cells(NextRow, 3) = TextDOB.Text

Cells(NextRow, 5) = TextAnniver.Text

Cells(NextRow, 6) = TextAdd1.Text

Cells(NextRow, 7) = TextAdd2.Text

Cells(NextRow, 8) = TextAdd3.Text

Cells(NextRow, 9) = TextCity.Text

Cells(NextRow, 10) = ComboState.Text

Cells(NextRow, 11) = TextPin.Text

Cells(NextRow, 12) = TextWorkPh.Text

Cells(NextRow, 13) = TextHomePh.Text

Cells(NextRow, 14) = TextEmail.Text

' Clear the controls for the next entry

TextName.Text = ""

OptionMale = True

OptionUnMarried = True

TextDOB.Text = ""

TextAnniver.Text = ""

TextAdd1.Text = ""

TextAdd2.Text = ""

TextAdd3.Text = ""

TextCity.Text = ""

ComboState.Text = ""

TextPin.Text = ""

TextWorkPh.Text = ""

TextHomePh.Text = ""

TextEmail.Text = ""

TextName.SetFocus

End Sub

6.       Activate Excel and click the CommandButton again to display the UserForm and then re-run the procedure again.

You’ll find that the UserForm controls now function correctly. You can use them to add new names to the list in the worksheet.

Here’s how the SaveButton_Click procedure works: First, the procedure makes sure that the proper worksheet (Sheet1) is active. It then uses Excel’s COUNTA function to determine the next blank cell in column A. Next, it transfers the text from the TextBox control to column A. It then uses a series of If statements to determine which OptionButton was selected and writes the appropriate text (Male, Female, or Unknown) to column B. This is how all TextBox and Option button work. Finally, the dialog box is reset to make it ready for the next entry. Notice that clicking Save doesn’t close the dialog box. To end data entry (and unload the UserForm), click the Exit button.

In Next Post we will talk about Some more about a UserForm