Chapter 20: Creating Advanced User Forms
|
| < Day Day Up > |
|
User forms are a critical part of Microsoft Excel programming in that they provide a surface that is totally under your control with which you can interact with a user. This makes it possible to build more complex Excel applications. Forms can also be used to collect and verify information from a user before it's entered into a worksheet. They can also be used as part of an add-in to display options and control execution of a particular task. In this chapter, you'll learn how to build a user form that allows a user to input data into the worksheet, plus how to build an add-in that invokes a multi-step wizard that creates a chart based on selections made by a user.
Capturing Information
Entering information into a worksheet can be painful sometimes. It's difficult to ensure that the data is valid and is properly formatted. It can also be difficult to ensure that the data is added at the proper location. In this example, you'll learn how to construct a simple form that accepts data from the user and stores it in a worksheet.
Form Application Overview
The Excel worksheet used in this sample application merely records six pieces of information about a customer at The Garden Company: CustomerId, Name, City, State, ZipCode, and DateAdded. (See Figure 20-1.)
Figure 20-1: A simple worksheet tracks customer information such as name and address and the date the customer was added.
This form is started by running a macro and remains up until the user explicitly closes the form. Buttons on the form control which row of the worksheet is displayed through the form, and the form itself allows the user to enter or edit any data stored in a particular row.
| Note | CD-ROM The complete source code for this example is found on the Companion CD for this book in FormApp.xls. Rather than entering each code listing found in this chapter, you should load the sample file, which includes some sample data you can use for testing. |
Designing a Form
For most data-driven forms, you should place the fields in a single column with labels next to each field. This arrangement makes it easy for the user to find information on the form. However, with some fields, such as those that are part of an address, you might find it better to arrange the fields more intuitively, such as placing the City, State, and ZipCode fields on the same line.
It's important to note that the placement of the fields on the form is completely independent of the code that accesses it. If you wished to arrange all of the fields in a circle, it wouldn't make a bit of difference to your code. While this fact is something that might seem obvious, it was the revolutionary concept that started the Microsoft Visual Basic revolution.
| Tip | Work with What Works |
Follow these steps to create a form that will interact with the user:
-
Start the Visual Basic Editor, and choose Insert, UserForm from the main menu. This will create a new UserForm object in your application.
-
From the Toolbox, drag a TextBox control and a Label control for each column in the worksheet. Drag a ComboBox control to hold the list of states. You might have to adjust the size of the user form to accommodate the controls.
Tip Double-Click to Save Time
If you wish add multiple copies of the same control to a user form, double-click the control in the toolbox. The mouse pointer will change to reflect the selected control. You may then draw multiple controls on the user form. When you're finished adding that particular control, you may double-click another control in the toolbox to add multiple copies of that control or click the arrow in the toolbox to return the mouse pointer to normal. -
Use the Properties window to change the Name property of each text box to reflect the database fields (CustomerId, CustomerName, City, ZipCode, and DateAdded). Change the Name property of the combo box control to State. Also change the Caption property for each Label control to something more descriptive. (See Figure 20-2.)
Figure 20-2: Add TextBox and Label controls for each column in the worksheet and their properties as directed. -
Add CommandButton controls to the user form that will allow the user to scroll through the rows. Change the Caption property to read First, Previous, Next, and Last. Leave a space between the Previous and Next controls.
Tip Controls Have Freedom of Movement
Once a control is on the form, you can select it and move it anywhere on the form you wish. You can also do the same thing with multiple controls by clicking the form and dragging to select the group of controls you want to move. Then you can drag the selected group around on the form. -
Add a TextBox control in between the Previous and Next controls. Change the Name property to RowNumber. Set the Text property to 2.
-
Add three more CommandButton controls. Change the Caption property of the first one to Save, the second one to Cancel, and the last one to Add.
-
Change the Enabled property on the Save and Cancel CommandButton controls to False.
-
Once all of the controls have been added, you can tweak their sizes and exact placement on the form until you find a pleasing arrangement. (See Figure 20-3.)
Figure 20-3: Finishing the form layout.
Displaying Data
With the form constructed, it's time to copy data from the worksheet to the form. The RowNumber text box contains the number of the row that should be displayed on the form, so the real trick is to convert the value in the RowNumber text box into a value that can be used to extract the data from the worksheet using the Cells method.
The following program listing shows the GetData routine, which is located in the module associated with the user form. GetData copies the data from the currently active worksheet to the user form. After declaring a temporary variable r to hold the current row, the routine verifies that the value in the RowNumber control is numeric. This step is important because the user could type any value into this field.
Private Sub GetData()
Dim r As Long
If IsNumeric(RowNumber.Text) Then
r = CLng(RowNumber.Text)
Else
ClearData
MsgBox "Illegal row number"
Exit Sub
End If
If r > 1 And r <= LastRow Then
CustomerId.Text = FormatNumber(Cells(r, 1), 0)
CustomerName.Text = Cells(r, 2)
City.Text = Cells(r, 3)
State.Text = Cells(r, 4)
Zip.Text = Cells(r, 5)
DateAdded.Text = FormatDateTime(Cells(r, 6), vbShortDate)
DisableSave
ElseIf r = 1 Then
ClearData
Else
ClearData
MsgBox "Invalid row number"
End If
End Sub
Knowing that RowNumber contains a numeric value, the CLng function is used to convert the value in RowNumber into the variable r. The rest of the code merely uses r to extract the information from the proper row and copy it to the correct field. Otherwise, a message box will be displayed to the user indicating that the row number value is invalid. The ClearData routine simply assigns an empty string to each field on the form to clear out any values that might have already been displayed on the form. (Remember that the ComboBox control can't be set to an empty string and should be set to a valid state value.)
Private Sub ClearData()
CustomerId.Text = ""
CustomerName.Text = ""
City.Text = ""
State.Text = "AK"
Zip.Text = ""
DateAdded.Text = ""
End Sub
Simply because the row number is numeric doesn't mean that it's safe to pass the number to the Cells method. You can add the constant LastRow to the start of the user form module like this while testing this routine. (Later in this chapter, you'll see how to determine the real last row of data in the worksheet, and you'll convert this constant to a module level variable.)
Const LastRow = 20
Using this value, you can verify that the row number is always in the range of 2 to LastRow, thus ensuring that the value in r always points to a valid row on the worksheet.
Remember that we have to handle the value of 1 as a special case because it's possible that the user has entered a 1 into the RowNumber text box as part of entering a number beginning with 1, such as 12 or 123. The easiest way to handle this issue is to simply clear the form by calling ClearData without issuing an error message.
Notice that the FormatNumber routine is used to convert the value in the first column to a number rather than simply assigning the value directly to the text box control. This technique ensures that the value is properly formatted in the field.
The same argument applies to the date data from the sixth column. The FormatDateTime function ensures that the data is properly formatted. While the function isn't specifically needed, it serves to remind you that you aren't dealing with text data.
Once the data is loaded onto the form, the Save and Cancel buttons are disabled by calling the DisableSave routine. These buttons are enabled only when the user changes a piece of information on the form.
Private Sub DisableSave()
CommandButton5.Enabled = False
CommandButton6.Enabled = False
End Sub
To hook the GetData routine into the form, switch from the code view of the user form to the object view showing the graphical representation of the form. Double-clicking the RowNumber control will take you back to the code view, but with one minor exception: the cursor will be placed in the middle of a new routine named RowNumber_Change.
Inside the new event, add a call to the GetData routine. This means that any time the data in the RowNumber control changes, the data shown in the form will be updated.
Private Sub RowNumber_Change()
GetData
End Sub
To test the routine, choose Run, Run Sub/UserForm from the main menu or press the F5 key. Then enter a row number into the RowNumber control. You will notice that the data from the appropriate row will be displayed. Also notice that it's impossible to enter a bad value for the row without generating an error message.
Navigating The Worksheet
Clicking any of the four navigation buttons should automatically adjust the value in the RowNumber text box. Then, because the value in RowNumber has been changed, the RowNumber_Change event will be fired and the currently displayed row will be updated.
Each of the four buttons represents a slightly different situation. The code for the First button is the simplest in that only a simple assignment statement is necessary to set RowNumber to 2. As with the RowNumber text box, the easiest way to edit the code for the appropriate event is to double-click the graphical control. The Visual Basic Editor will automatically add the event, and you can enter this line of code to complete it.
RowNumber.Text = "2"
| Tip | Test As You Go |
The Prev and Next buttons are a little more complicated because you need to compute the value of the previous or next row based on the value of the current row. Like the GetData routine, this routine (shown in the following listing) begins by verifying that the value contained in RowNumber is numeric.
Private Sub CommandButton2_Click()
Dim r As Long
If IsNumeric(RowNumber.Text) Then
r = CLng(RowNumber.Text)
r = r - 1
If r > 1 And r <= LastRow Then
RowNumber.Text = FormatNumber(r, 0)
End If
End If
End Sub
Once the routine has a numeric value, it computes the new position by subtracting 1 (or adding 1 to find the next row). Finally, if the resulting row number is in the range of 2 to LastRow -1, the value is saved into the RowNumber text box. The assignment will trigger the Change event for the RowNumber control, and the new information will be loaded.
Jumping to the last row is a bit more difficult because the concept of the last row is somewhat nebulous. After all, just because a worksheet can handle 65,536 rows of data doesn't mean that the user of that application wants to view rows that far down. Instead, it makes sense to look through the worksheet to find the last row with a value in the first column and treat that as the last row.
To make the last row dynamic, a few changes need to be made to the program. First the LastRow constant needs to be switched to a variable like this:
Public LastRow As Long
Then the constant needs to be assigned an initial value when the user form is initially loaded. There are two ways to do this. The easiest way is just to assign it a valid row number such as 3 and then call GetData to load the initial values into the form. So, use the following code to create the UserForm_Initialize event.
Private Sub UserForm_Initialize()
GetData
End Sub
If users want to see the last line in the form, they will need to press the Last button. There are several ways to locate the last row in response to the user clicking the Last button. One way would be to scan through all of the data looking for the first empty cell in column one each time the Last button was clicked.
A better way would be to scan through the worksheet and locate the first blank cell in column 1 and assign the value to LastRow, which is what the routine shown in the following listing does. This routine is located in the user form module.
Private Function FindLastRow()
Dim r As Long
r = 2
Do While r < 65536 And Len(Cells(r, 1).Text) > 0
r = r + 1
Loop
FindLastRow = r
End Function
The FindLastRow function scans through the worksheet to find the first cell that doesn't have a value. A simple While loop iterates through each cell in column one of the worksheet, and the length of the return value is tested to see if the length is zero. If the length is zero, the loop will end and r will point to the last row in the worksheet, which is the first blank row following the data in the worksheet.
Then you can set the LastRow variable by adding the following line to the UserForm_Initialize event.
LastRow = FindLastRow
The FindLastRow function can also be used in the event associated with the Last button to update the LastRow variable as well as set the value for the RowNumber control.
Private Sub CommandButton4_Click()
LastRow = FindLastRow - 1
RowNumber.Text = FormatNumber(LastRow, 0)
End Sub
Editing Data
At this point, you can view any row of data in the worksheet, but any changes you make in the data displayed on the form aren't saved in the worksheet. There are a lot of different techniques you can use, but here's one that should work well for you.
In this approach, the data displayed on the form is kept separate from the cells on the worksheet until the user explicitly presses either the Save or the Cancel button. Pressing the Save button should copy the data from the form to the worksheet, whereas pressing Cancel should reload the data from the worksheet, overwriting any changes in the user form that may have been made by the user. Both the Save and Cancel buttons should be disabled until the data on the form is actually changed.
The easiest way to disable these buttons is to set their Enabled property to False. Then change the Enabled property to True once one of the values in the field changes. You can reduce the amount of work by creating two subroutines, one named EnableSave and one named DisableSave, which enable and disable the command buttons associated with Save and Cancel, respectively. Then, in the Change event associated with the text boxes that contain data, add a call to the EnableSave subroutine. This setting means that any change to the data will mark the entire form as dirty, meaning that the data in the form is different from the data on the worksheet.
Because loading the data directly from the source means that the data is clean, the Save and Cancel buttons should call the DisableSave routine. This call should be placed only after the data is loaded onto the form because it's possible that the user might not have entered a valid row number and GetData might not actually reload any data.
The PutData routine found in the user form module (shown in the following listing) is similar to the GetData routine in that all the validations used to ensure that the value in RowNumber is valid are included. The main difference between the two routines is that the GetData routine copies information from the worksheet, whereas the PutData routine copies data to the worksheet.
Private Sub PutData()
Dim r As Long
If IsNumeric(RowNumber.Text) Then
r = CLng(RowNumber.Text)
Else
MsgBox "Illegal row number"
Exit Sub
End If
If r > 1 And r < LastRow Then
Cells(r, 1) = CustomerId.Text
Cells(r, 2) = CustomerName.Text
Cells(r, 3) = City.Text
Cells(r, 4) = State.Text
Cells(r, 5) = Zip.Text
Cells(r, 6) = DateAdded.Text
DisableSave
Else
MsgBox "Invalid row number"
End If
End Sub
The error checking isn't absolutely necessary, but it probably is a good idea just in case someone put an invalid value in the RowNumber text box, jumped to another application, and then came back. In that scenario, it's possible to enter a different value in the RowNumber text box without retrieving any data.
Notice that after the data is saved to the worksheet, DisableSave routine is called. This is necessary because the data on the user form now represents the same data stored on the worksheet.
Adding Data
Pressing the Add button calls the CommandButton7_Click event, which displays the first blank row at the end of the worksheet. Because the LastRow variable points to this row, it's merely a matter of setting the Text property of the RowNumber control to this value using code like this:
Private Sub CommandButton7_Click()
RowNumber.Text = FormatNumber(LastRow, 0)
End Sub
Validating Data
At this point, the form is fully capable of capturing data from the user and inserting it into the worksheet. The form also allows the user to edit the values already stored in the worksheet. The only limitation is that none of the data is validated for correctness.
For instance, it's possible to enter an invalid date as part of the DateAdded field. Also, there are no checks to ensure that the CustomerId value is numeric. Finally, it's possible to enter the wrong two-character state code. Here are some techniques that you can use to ensure that the data is valid before it reaches your worksheet.
The first technique involves using the KeyPress event to ensure that the user can enter only a particular type of information. For example, you could ensure that the user can only enter numbers into the CustomerId control using this code:
Private Sub CustomerId_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
If KeyAscii < Asc("0") Or KeyAscii > Asc("9") Then
KeyAscii = 0
End If
End Sub
| Tip | Defining Events |
Another approach involves using the Exit event. In the Exit event associated with a particular control on the user form, you can determine if the user made an error and highlight the background to give the user a visual clue. You can also display a message box that contains a description of the error using code like this:
Private Sub DateAdded_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If Not IsDate(DateAdded.Text) Then
DateAdded.BackColor = &HFF&
MsgBox "Illegal date value"
Cancel = True
Else
DateAdded.BackColor = &H80000005
End If
End Sub
One nice feature of the Exit event is that if you set the Cancel argument to True, the user will be unable to switch the focus to a different control until the text box contains a proper date.
Remember that you also need to set the background color to Window Background (&H80000005) if there isn't an error, to reset any previous error conditions. This is handled by the Else clause.
The final technique used in this application prevents errors by substituting a combo box control in place the text box control for State. Because the user is limited to choosing one value from the provided list of values, it becomes impossible to enter invalid data.
By setting the MatchRequired property of the combo box control to True, the user will be prevented from leaving the control until the input matches one of the values in the List. Another way to ensure that only a valid value is selected is to set the Style property of the combo box control to fmStyleDropDownList, which forces the control to operate as a list box, where the user can only choose a value from the specified list of values in the drop-down list instead of typing a value that might not be on the list.
In either case, a routine like the following AddStates routine is necessary to initialize the combo box control. This routine would typically be called from the user form's Initialize event.
| Note | CD-ROM The full list of the states can be found in the AddStates routine in the sample program.
Private Sub AddStates()
|
Displaying the User Form
The final step in this process is to create a simple macro that displays the user form. In this case, adding the following subroutine to the ThisWorkbook object in the Visual Basic Editor is all that's required to show the form. Any time the user wants to use this form, he simply has to run this macro.
Public Sub ShowForm()
UserForm1.Show vbModal
End Sub
As the vbModal value implies, once the form is displayed, it remains on the screen, preventing the user from accessing any part of the Excel worksheet underneath it. If it's important to provide this level of access, you can switch the vbModal value to vbModeless. Then the user will be able to switch between the form and the worksheet. (See Figure 20-4.)
Figure 20-4: The user can switch between the form and Excel if the form is displayed using vbModeless value.
| Warning | Use the vbModeless value with care. In this example, allowing the user to access the underlying worksheet also permits the user to change the data that's currently displayed on the user form. If this happens and the user presses the Save button, any changes that the user might have made directly to the row will be lost. |
|
| < Day Day Up > |
|
EAN: 2147483647
Pages: 161