EDN Admin
Well-known member
<p align=left><font face=Arial size=2>I am using the example below to Create a userform for adding and editing transactions in a spreadsheet. The adding part works fine but Im having a problem editing and saving. Also what code i am using for the save and cancel buttons. please help. I am new to VB</font>
<p align=left>
<p align=left>
<p align=left>Chapter 20: Creating Advanced User Forms
<h4 class=dtH1>Contents</h4>
<a title="http://msdn.microsoft.com/en-us/library/aa192538(office.11).aspx#officeexcel2003programminginsideoutch20_capturinginformation http://msdn.microsoft.com/en-us/library/aa192538(office.11).aspx#officeexcel2003programminginsideoutch20_capturinginformation Capturing Information
<a title="http://msdn.microsoft.com/en-us/library/aa192538(office.11).aspx#officeexcel2003programminginsideoutch20_buildingawizard http://msdn.microsoft.com/en-us/library/aa192538(office.11).aspx#officeexcel2003programminginsideoutch20_buildingawizard Building a Multi-Step Wizard
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 its 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, youll 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.
<h2 class=dtH1><a name="officeexcel2003programminginsideoutch20_capturinginformation Capturing Information
Entering information into a worksheet can be painful sometimes. Its 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, youll learn how to construct a simple form that accepts data from the user and stores it in a worksheet.
<h3 class=dtH1>Form Application Overview</h3>
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.)
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.
<p class=fig>
<a id="ctl00_rs1_mainContentContainer_ctl02 http://msdn.microsoft.com/en-us/library/Aa192538.officeexcel2003programminginsideoutch20fig1(en-us,office.11).gif
<p class=label><b>Figure 20-1. A simple worksheet tracks customer information such as name and address and the date the customer was added.</b>
<h3 class=dtH1>Designing a Form</h3>
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.
Its 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 wouldnt 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.
<blockquote class=dtBlock><b class=le>Tip: Work with What Works </b>When designing user forms, take a look at the various windows and dialog boxes built into Excel and other applications for design ideas.</blockquote>
Follow these steps to create a form that will interact with the user:
<ol type=1>
Start the Visual Basic Editor, and choose Insert, UserForm from the main menu. This will create a new <i>UserForm</i> object in your application.
From the Toolbox, drag a <i>TextBox</i> control and a <i>Label</i> control for each column in the worksheet. Drag a <i>ComboBox</i> control to hold the list of states. You might have to adjust the size of the user form to accommodate the controls.
<blockquote class=dtBlock><b class=le>Tip: Double-Click to Save Time </b>If you wish to 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 youre 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.</blockquote>
Use the Properties window to change the <i>Name</i> property of each text box to reflect the database fields (<b>CustomerId</b>, <b>CustomerName</b>, <b>City</b>, <b>ZipCode</b>, and <b>DateAdded</b>). Change the <i>Name</i> property of the combo box control to <b>State</b>. Also change the <i>Caption</i> property for each <i>Label</i> control to something more descriptive. (See Figure 20-2.)
<p class=fig1>
<a id="ctl00_rs1_mainContentContainer_ctl03 http://msdn.microsoft.com/en-us/library/Aa192538.officeexcel2003programminginsideoutch20fig2(en-us,office.11).gif
<p class=label><b>Figure 20-2. Add TextBox and Label controls for each column in the worksheet and their properties as directed.</b>
Add <i>CommandButton</i> controls to the user form that will allow the user to scroll through the rows. Change the <i>Caption</i> property to read <b>First</b>, <b>Previous</b>, <b>Next</b>, and <b>Last</b>. Leave a space between the <i>Previous</i> and <i>Next</i> controls.
<blockquote class=dtBlock><b class=le>Tip: Controls Have Freedom of Movement </b>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.</blockquote>
Add a <i>TextBox</i> control in between the <i>Previous</i> and <i>Next</i> controls. Change the <i>Name</i> property to <b>RowNumber</b>. Set the <i>Text</i> property to <b>2</b>.
Add three more <i>CommandButton</i> controls. Change the <i>Caption</i> property of the first one to <b>Save</b>, the second one to <b>Cancel</b>, and the last one to <b>Add</b>.
Change the <i>Enabled</i> property on the <i>Save</i> and <i>Cancel</i> <i>CommandButton</i> controls to <b>False</b>.
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.)
<p class=fig1>
<a id="ctl00_rs1_mainContentContainer_ctl04 http://msdn.microsoft.com/en-us/library/Aa192538.officeexcel2003programminginsideoutch20fig3(en-us,office.11).gif
<p class=label><b>Figure 20-3. Finishing the form layout.</b> </ol>
<h3 class=dtH1>Displaying Data</h3>
With the form constructed, its time to copy data from the worksheet to the form. The <i>RowNumber</i> 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 <i>RowNumber</i> text box into a value that can be used to extract the data from the worksheet using the <i>Cells</i> method.
The following program listing shows the <i>GetData</i> routine, which is located in the module associated with the user form. <i>GetData</i> copies the data from the currently active worksheet to the user form. After declaring a temporary variable <i>r</i> to hold the current row, the routine verifies that the value in the <i>RowNumber</i> control is numeric. This step is important because the user could type any value into this field.
<div class=libCScode id="ctl00_rs1_mainContentContainer_ctl05_
<div class=CodeSnippetTitleBar>
<div class=CodeDisplayLanguage>
<div class=CopyCodeButton><a class=copyCode>Copy Code <pre class=libCScode id="ctl00_rs1_mainContentContainer_ctl05 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
[/code]
Knowing that <i>RowNumber</i> contains a numeric value, the <i>CLng</i> function is used to convert the value in <i>RowNumber</i> into the variable <i>r</i>. The rest of the code merely uses <i>r</i> 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 <i>ClearData</i> 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 <i>ComboBox</i> control cant be set to an empty string and should be set to a valid state value.)
<div class=libCScode id="ctl00_rs1_mainContentContainer_ctl06_
<div class=CodeSnippetTitleBar>
<div class=CodeDisplayLanguage>
<div class=CopyCodeButton><a class=copyCode>Copy Code <pre class=libCScode id="ctl00_rs1_mainContentContainer_ctl06 Private Sub ClearData()
CustomerId.Text = ""
CustomerName.Text = ""
City.Text = ""
State.Text = "AK"
Zip.Text = ""
DateAdded.Text = ""
End Sub
[/code]
Simply because the row number is numeric doesnt mean that its safe to pass the number to the <i>Cells</i> method. You can add the constant <i>LastRow</i> to the start of the user form module like this while testing this routine. (Later in this chapter, youll see how to determine the real last row of data in the worksheet, and youll convert this constant to a module level variable.)
<div class=libCScode id="ctl00_rs1_mainContentContainer_ctl07_
<div class=CodeSnippetTitleBar>
<div class=CodeDisplayLanguage>
<div class=CopyCodeButton><a class=copyCode>Copy Code <pre class=libCScode id="ctl00_rs1_mainContentContainer_ctl07 Const LastRow = 20
[/code]
Using this value, you can verify that the row number is always in the range of <i>2</i> to <i>LastRow</i>, thus ensuring that the value in <i>r</i> always points to a valid row on the worksheet.
Remember that we have to handle the value of 1 as a special case because its 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 <i>ClearData</i> without issuing an error message.
Notice that the <i>FormatNumber</i> 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 <i>FormatDateTime</i> function ensures that the data is properly formatted. While the function isnt specifically needed, it serves to remind you that you arent dealing with text data.
Once the data is loaded onto the form, the Save and Cancel buttons are disabled by calling the <i>DisableSave</i> routine. These buttons are enabled only when the user changes a piece of information on the form.
<div class=libCScode id="ctl00_rs1_mainContentContainer_ctl08_
<div class=CodeSnippetTitleBar>
<div class=CodeDisplayLanguage>
<div class=CopyCodeButton><a class=copyCode>Copy Code <pre class=libCScode id="ctl00_rs1_mainContentContainer_ctl08 Private Sub DisableSave()
CommandButton5.Enabled = False
CommandButton6.Enabled = False
End Sub
[/code]
To hook the <i>GetData</i> 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 <i>RowNumber</i> 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 <i>RowNumber_Change</i>.
Inside the new event, add a call to the <i>GetData</i> routine. This means that any time the data in the <i>RowNumber</i> control changes, the data shown in the form will be updated.
<div class=libCScode id="ctl00_rs1_mainContentContainer_ctl09_
<div class=CodeSnippetTitleBar>
<div class=CodeDisplayLanguage>
<div class=CopyCodeButton><a class=copyCode>Copy Code <pre class=libCScode id="ctl00_rs1_mainContentContainer_ctl09 Private Sub RowNumber_Change()
GetData
End Sub
[/code]
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 <i>RowNumber</i> control. You will notice that the data from the appropriate row will be displayed. Also notice that its impossible to enter a bad value for the row without generating an error message.
<h3 class=dtH1>Navigating The Worksheet</h3>
Clicking any of the four navigation buttons should automatically adjust the value in the <i>RowNumber</i> text box. Then, because the value in <i>RowNumber</i> has been changed, the <i>RowNumber_Change</i> 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 <i>RowNumber</i> to <i>2</i>. As with the <i>RowNumber</i> 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.
<div class=libCScode id="ctl00_rs1_mainContentContainer_ctl10_
<div class=CodeSnippetTitleBar>
<div class=CodeDisplayLanguage>
<div class=CopyCodeButton><a class=copyCode>Copy Code <pre class=libCScode id="ctl00_rs1_mainContentContainer_ctl10 RowNumber.Text = "2"
[/code]
<blockquote class=dtBlock><b class=le>Tip: Test As You Go </b>As you create the code for each button, take time to run the program and see the results. One of the strengths of Visual Basic is that you can quickly test your programs. Its far easier to debug five or 10 lines of code that you just added than to wait until youve added a few hundred lines of code.</blockquote>
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 <i>GetData</i> routine, this routine (shown in the following listing) begins by verifying that the value contained in <i>RowNumber</i> is numeric.
<div class=libCScode id="ctl00_rs1_mainContentContainer_ctl11_
<div class=CodeSnippetTitleBar>
<div class=CodeDisplayLanguage>
<div class=CopyCodeButton><a class=copyCode>Copy Code <pre class=libCScode id="ctl00_rs1_mainContentContainer_ctl11 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
[/code]
Once the routine has a numeric value, it computes the new position by subtracting <i>1</i> (or adding <i>1</i> to find the next row). Finally, if the resulting row number is in the range of <i>2</i> to <i>LastRow</i> ?<i>1</i>, the value is saved into the RowNumber text box. The assignment will trigger the event for the <i>RowNumber</i> 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 doesnt 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 <i>LastRow</i> constant needs to be switched to a variable like this:
<div class=libCScode id="ctl00_rs1_mainContentContainer_ctl12_
<div class=CodeSnippetTitleBar>
<div class=CodeDisplayLanguage>
<div class=CopyCodeButton><a class=copyCode>Copy Code <pre class=libCScode id="ctl00_rs1_mainContentContainer_ctl12 Private Sub UserForm_Initialize()
GetData
End Sub
[/code]
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 <i>LastRow</i>, which is what the routine shown in the following listing does. This routine is located in the user form module.
<div class=libCScode id="ctl00_rs1_mainContentContainer_ctl13_
<div class=CodeSnippetTitleBar>
<div class=CodeDisplayLanguage>
<div class=CopyCodeButton><a class=copyCode>Copy Code <pre class=libCScode id="ctl00_rs1_mainContentContainer_ctl13 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
[/code]
The <i>FindLastRow</i> function scans through the worksheet to find the first cell that doesnt have a value. A simple <i>While</i> 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 <i>r</i> 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 <i>LastRow</i> variable by adding the following line to the <i>UserForm_Initialize</i> event.
<div class=libCScode id="ctl00_rs1_mainContentContainer_ctl14_
<div class=CodeSnippetTitleBar>
<div class=CodeDisplayLanguage>
<div class=CopyCodeButton><a class=copyCode>Copy Code <pre class=libCScode id="ctl00_rs1_mainContentContainer_ctl14 LastRow = FindLastRow
[/code]
The <i>FindLastRow</i> function can also be used in the event associated with the Last button to update the <i>LastRow</i> variable as well as set the value for the <i>RowNumber</i> control.
<div class=libCScode id="ctl00_rs1_mainContentContainer_ctl15_
<div class=CodeSnippetTitleBar>
<div class=CodeDisplayLanguage>
<div class=CopyCodeButton><a class=copyCode>Copy Code <pre class=libCScode id="ctl00_rs1_mainContentContainer_ctl15 Private Sub CommandButton4_Click()
LastRow = FindLastRow - 1
RowNumber.Text = FormatNumber(LastRow, 0)
End Sub
[/code]
<h3 class=dtH1>Editing Data</h3>
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 arent saved in the worksheet. There are a lot of different techniques you can use, but heres 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 <i>Enabled</i> property to <i>False</i>. Then change the <i>Enabled</i> property to <i>True</i> once one of the values in the field changes. You can reduce the amount of work by creating two subroutines, one named <i>EnableSave</i> and one named <i>DisableSave</i>, which enable and disable the command buttons associated with Save and Cancel, respectively. Then, in the <i>Change</i> event associated with the text boxes that contain data, add a call to the <i>EnableSave</i> subroutine. This setting means that any change to the data will mark the entire form as <i>dirty</i>, 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 <i>DisableSave</i> routine. This call should be placed only after the data is loaded onto the form because its possible that the user might not have entered a valid row number and <i>GetData</i> might not actually reload any data.
The <i>PutData</i> routine found in the user form module (shown in the following listing) is similar to the <i>GetData</i> routine in that all the validations used to ensure that the value in <i>RowNumber</i> is valid are included. The main difference between the two routines is that the <i>GetData</i> routine copies information <i>from</i> the worksheet, whereas the <i>PutData</i> routine copies data <i>to</i> the worksheet.
<div class=libCScode id="ctl00_rs1_mainContentContainer_ctl16_
<div class=CodeSnippetTitleBar>
<div class=CodeDisplayLanguage>
<div class=CopyCodeButton><a class=copyCode>Copy Code <pre class=libCScode id="ctl00_rs1_mainContentContainer_ctl16 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
[/code]
The error checking isnt absolutely necessary, but it probably is a good idea just in case someone put an invalid value in the <i>RowNumber</i> text box, jumped to another application, and then came back. In that scenario, its 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, the <i>DisableSave</i> routine is called. This is necessary because the data on the user form now represents the same data stored on the worksheet.
<h3 class=dtH1>Adding Data</h3>
Pressing the Add button calls the <i>CommandButton7_Click</i> event, which displays the first blank row at the end of the worksheet. Because the <i>LastRow</i> variable points to this row, its merely a matter of setting the <i>Text</i> property of the <i>RowNumber</i> control to this value using code like this:
<div class=libCScode id="ctl00_rs1_mainContentContainer_ctl17_
<div class=CodeSnippetTitleBar>
<div class=CodeDisplayLanguage>
<div class=CopyCodeButton><a class=copyCode>Copy Code <pre class=libCScode id="ctl00_rs1_mainContentContainer_ctl17 Private Sub CommandButton7_Click()
RowNumber.Text = FormatNumber(LastRow, 0)
End Sub
[/code]
<h3 class=dtH1>Validating Data</h3>
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, its possible to enter an invalid date as part of the <i>DateAdded</i> field. Also, there are no checks to ensure that the <i>CustomerId</i> value is numeric. Finally, its 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 <i>KeyPress</i> 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 <i>CustomerId</i> control using this code:
<div class=libCScode id="ctl00_rs1_mainContentContainer_ctl18_
<div class=CodeSnippetTitleBar>
<div class=CodeDisplayLanguage>
<div class=CopyCodeButton><a class=copyCode>Copy Code <pre class=libCScode id="ctl00_rs1_mainContentContainer_ctl18 Private Sub CustomerId_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
If KeyAscii < Asc("0") Or KeyAscii > Asc("9") Then
KeyAscii = 0
End If
End Sub
[/code]
<blockquote class=dtBlock><b class=le>Tip: Defining Events </b>Double-clicking the <i>CustomerId</i> control on the user form will automatically take you to the <i>CustomerId_Change</i> event. If the event doesnt exist, it will automatically be created. If you want to handle a different event, simply choose the name of the event from the drop-down list at the top of the code window and the Visual Basic Editor will automatically create a blank event with the appropriate parameters.</blockquote>
Another approach involves using the <i>Exit</i> event. In the <i>Exit</i> 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:
<div class=libCScode id="ctl00_rs1_mainContentContainer_ctl19_
<div class=CodeSnippetTitleBar>
<div class=CodeDisplayLanguage>
<div class=CopyCodeButton><a class=copyCode>Copy Code <pre class=libCScode id="ctl00_rs1_mainContentContainer_ctl19 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
[/code]
One nice feature of the <i>Exit</i> event is that if you set the <i>Cancel</i> argument to <i>True</i>, 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 isnt an error, to reset any previous error conditions. This is handled by the <i>Else</i> clause.
The final technique used in this application prevents errors by substituting a combo box control in place of the text box control for <i>State</i>. 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 <i>MatchRequired</i> 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 <i>List</i>. Another way to ensure that only a valid value is selected is to set the <i>Style</i> property of the combo box control to <i>fmStyleDropDownList</i>, 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 <i>AddStates</i> routine is necessary to initialize the combo box control. This routine would typically be called from the user forms <i>Initialize</i> event.
<div class=libCScode id="ctl00_rs1_mainContentContainer_ctl20_
<div class=CodeSnippetTitleBar>
<div class=CodeDisplayLanguage>
<div class=CopyCodeButton><a class=copyCode>Copy Code <pre class=libCScode id="ctl00_rs1_mainContentContainer_ctl20 Private Sub AddStates()
State.AddItem "AK"
State.AddItem "AL"
State.AddItem "AR"
State.AddItem "AZ"
End Sub
[/code]
View the full article
<p align=left>
<p align=left>
<p align=left>Chapter 20: Creating Advanced User Forms
<h4 class=dtH1>Contents</h4>
<a title="http://msdn.microsoft.com/en-us/library/aa192538(office.11).aspx#officeexcel2003programminginsideoutch20_capturinginformation http://msdn.microsoft.com/en-us/library/aa192538(office.11).aspx#officeexcel2003programminginsideoutch20_capturinginformation Capturing Information
<a title="http://msdn.microsoft.com/en-us/library/aa192538(office.11).aspx#officeexcel2003programminginsideoutch20_buildingawizard http://msdn.microsoft.com/en-us/library/aa192538(office.11).aspx#officeexcel2003programminginsideoutch20_buildingawizard Building a Multi-Step Wizard
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 its 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, youll 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.
<h2 class=dtH1><a name="officeexcel2003programminginsideoutch20_capturinginformation Capturing Information
Entering information into a worksheet can be painful sometimes. Its 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, youll learn how to construct a simple form that accepts data from the user and stores it in a worksheet.
<h3 class=dtH1>Form Application Overview</h3>
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.)
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.
<p class=fig>
<a id="ctl00_rs1_mainContentContainer_ctl02 http://msdn.microsoft.com/en-us/library/Aa192538.officeexcel2003programminginsideoutch20fig1(en-us,office.11).gif
<p class=label><b>Figure 20-1. A simple worksheet tracks customer information such as name and address and the date the customer was added.</b>
<h3 class=dtH1>Designing a Form</h3>
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.
Its 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 wouldnt 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.
<blockquote class=dtBlock><b class=le>Tip: Work with What Works </b>When designing user forms, take a look at the various windows and dialog boxes built into Excel and other applications for design ideas.</blockquote>
Follow these steps to create a form that will interact with the user:
<ol type=1>
Start the Visual Basic Editor, and choose Insert, UserForm from the main menu. This will create a new <i>UserForm</i> object in your application.
From the Toolbox, drag a <i>TextBox</i> control and a <i>Label</i> control for each column in the worksheet. Drag a <i>ComboBox</i> control to hold the list of states. You might have to adjust the size of the user form to accommodate the controls.
<blockquote class=dtBlock><b class=le>Tip: Double-Click to Save Time </b>If you wish to 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 youre 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.</blockquote>
Use the Properties window to change the <i>Name</i> property of each text box to reflect the database fields (<b>CustomerId</b>, <b>CustomerName</b>, <b>City</b>, <b>ZipCode</b>, and <b>DateAdded</b>). Change the <i>Name</i> property of the combo box control to <b>State</b>. Also change the <i>Caption</i> property for each <i>Label</i> control to something more descriptive. (See Figure 20-2.)
<p class=fig1>
<a id="ctl00_rs1_mainContentContainer_ctl03 http://msdn.microsoft.com/en-us/library/Aa192538.officeexcel2003programminginsideoutch20fig2(en-us,office.11).gif
<p class=label><b>Figure 20-2. Add TextBox and Label controls for each column in the worksheet and their properties as directed.</b>
Add <i>CommandButton</i> controls to the user form that will allow the user to scroll through the rows. Change the <i>Caption</i> property to read <b>First</b>, <b>Previous</b>, <b>Next</b>, and <b>Last</b>. Leave a space between the <i>Previous</i> and <i>Next</i> controls.
<blockquote class=dtBlock><b class=le>Tip: Controls Have Freedom of Movement </b>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.</blockquote>
Add a <i>TextBox</i> control in between the <i>Previous</i> and <i>Next</i> controls. Change the <i>Name</i> property to <b>RowNumber</b>. Set the <i>Text</i> property to <b>2</b>.
Add three more <i>CommandButton</i> controls. Change the <i>Caption</i> property of the first one to <b>Save</b>, the second one to <b>Cancel</b>, and the last one to <b>Add</b>.
Change the <i>Enabled</i> property on the <i>Save</i> and <i>Cancel</i> <i>CommandButton</i> controls to <b>False</b>.
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.)
<p class=fig1>
<a id="ctl00_rs1_mainContentContainer_ctl04 http://msdn.microsoft.com/en-us/library/Aa192538.officeexcel2003programminginsideoutch20fig3(en-us,office.11).gif
<p class=label><b>Figure 20-3. Finishing the form layout.</b> </ol>
<h3 class=dtH1>Displaying Data</h3>
With the form constructed, its time to copy data from the worksheet to the form. The <i>RowNumber</i> 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 <i>RowNumber</i> text box into a value that can be used to extract the data from the worksheet using the <i>Cells</i> method.
The following program listing shows the <i>GetData</i> routine, which is located in the module associated with the user form. <i>GetData</i> copies the data from the currently active worksheet to the user form. After declaring a temporary variable <i>r</i> to hold the current row, the routine verifies that the value in the <i>RowNumber</i> control is numeric. This step is important because the user could type any value into this field.
<div class=libCScode id="ctl00_rs1_mainContentContainer_ctl05_
<div class=CodeSnippetTitleBar>
<div class=CodeDisplayLanguage>
<div class=CopyCodeButton><a class=copyCode>Copy Code <pre class=libCScode id="ctl00_rs1_mainContentContainer_ctl05 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
[/code]
Knowing that <i>RowNumber</i> contains a numeric value, the <i>CLng</i> function is used to convert the value in <i>RowNumber</i> into the variable <i>r</i>. The rest of the code merely uses <i>r</i> 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 <i>ClearData</i> 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 <i>ComboBox</i> control cant be set to an empty string and should be set to a valid state value.)
<div class=libCScode id="ctl00_rs1_mainContentContainer_ctl06_
<div class=CodeSnippetTitleBar>
<div class=CodeDisplayLanguage>
<div class=CopyCodeButton><a class=copyCode>Copy Code <pre class=libCScode id="ctl00_rs1_mainContentContainer_ctl06 Private Sub ClearData()
CustomerId.Text = ""
CustomerName.Text = ""
City.Text = ""
State.Text = "AK"
Zip.Text = ""
DateAdded.Text = ""
End Sub
[/code]
Simply because the row number is numeric doesnt mean that its safe to pass the number to the <i>Cells</i> method. You can add the constant <i>LastRow</i> to the start of the user form module like this while testing this routine. (Later in this chapter, youll see how to determine the real last row of data in the worksheet, and youll convert this constant to a module level variable.)
<div class=libCScode id="ctl00_rs1_mainContentContainer_ctl07_
<div class=CodeSnippetTitleBar>
<div class=CodeDisplayLanguage>
<div class=CopyCodeButton><a class=copyCode>Copy Code <pre class=libCScode id="ctl00_rs1_mainContentContainer_ctl07 Const LastRow = 20
[/code]
Using this value, you can verify that the row number is always in the range of <i>2</i> to <i>LastRow</i>, thus ensuring that the value in <i>r</i> always points to a valid row on the worksheet.
Remember that we have to handle the value of 1 as a special case because its 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 <i>ClearData</i> without issuing an error message.
Notice that the <i>FormatNumber</i> 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 <i>FormatDateTime</i> function ensures that the data is properly formatted. While the function isnt specifically needed, it serves to remind you that you arent dealing with text data.
Once the data is loaded onto the form, the Save and Cancel buttons are disabled by calling the <i>DisableSave</i> routine. These buttons are enabled only when the user changes a piece of information on the form.
<div class=libCScode id="ctl00_rs1_mainContentContainer_ctl08_
<div class=CodeSnippetTitleBar>
<div class=CodeDisplayLanguage>
<div class=CopyCodeButton><a class=copyCode>Copy Code <pre class=libCScode id="ctl00_rs1_mainContentContainer_ctl08 Private Sub DisableSave()
CommandButton5.Enabled = False
CommandButton6.Enabled = False
End Sub
[/code]
To hook the <i>GetData</i> 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 <i>RowNumber</i> 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 <i>RowNumber_Change</i>.
Inside the new event, add a call to the <i>GetData</i> routine. This means that any time the data in the <i>RowNumber</i> control changes, the data shown in the form will be updated.
<div class=libCScode id="ctl00_rs1_mainContentContainer_ctl09_
<div class=CodeSnippetTitleBar>
<div class=CodeDisplayLanguage>
<div class=CopyCodeButton><a class=copyCode>Copy Code <pre class=libCScode id="ctl00_rs1_mainContentContainer_ctl09 Private Sub RowNumber_Change()
GetData
End Sub
[/code]
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 <i>RowNumber</i> control. You will notice that the data from the appropriate row will be displayed. Also notice that its impossible to enter a bad value for the row without generating an error message.
<h3 class=dtH1>Navigating The Worksheet</h3>
Clicking any of the four navigation buttons should automatically adjust the value in the <i>RowNumber</i> text box. Then, because the value in <i>RowNumber</i> has been changed, the <i>RowNumber_Change</i> 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 <i>RowNumber</i> to <i>2</i>. As with the <i>RowNumber</i> 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.
<div class=libCScode id="ctl00_rs1_mainContentContainer_ctl10_
<div class=CodeSnippetTitleBar>
<div class=CodeDisplayLanguage>
<div class=CopyCodeButton><a class=copyCode>Copy Code <pre class=libCScode id="ctl00_rs1_mainContentContainer_ctl10 RowNumber.Text = "2"
[/code]
<blockquote class=dtBlock><b class=le>Tip: Test As You Go </b>As you create the code for each button, take time to run the program and see the results. One of the strengths of Visual Basic is that you can quickly test your programs. Its far easier to debug five or 10 lines of code that you just added than to wait until youve added a few hundred lines of code.</blockquote>
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 <i>GetData</i> routine, this routine (shown in the following listing) begins by verifying that the value contained in <i>RowNumber</i> is numeric.
<div class=libCScode id="ctl00_rs1_mainContentContainer_ctl11_
<div class=CodeSnippetTitleBar>
<div class=CodeDisplayLanguage>
<div class=CopyCodeButton><a class=copyCode>Copy Code <pre class=libCScode id="ctl00_rs1_mainContentContainer_ctl11 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
[/code]
Once the routine has a numeric value, it computes the new position by subtracting <i>1</i> (or adding <i>1</i> to find the next row). Finally, if the resulting row number is in the range of <i>2</i> to <i>LastRow</i> ?<i>1</i>, the value is saved into the RowNumber text box. The assignment will trigger the event for the <i>RowNumber</i> 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 doesnt 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 <i>LastRow</i> constant needs to be switched to a variable like this:
<div class=libCScode id="ctl00_rs1_mainContentContainer_ctl12_
<div class=CodeSnippetTitleBar>
<div class=CodeDisplayLanguage>
<div class=CopyCodeButton><a class=copyCode>Copy Code <pre class=libCScode id="ctl00_rs1_mainContentContainer_ctl12 Private Sub UserForm_Initialize()
GetData
End Sub
[/code]
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 <i>LastRow</i>, which is what the routine shown in the following listing does. This routine is located in the user form module.
<div class=libCScode id="ctl00_rs1_mainContentContainer_ctl13_
<div class=CodeSnippetTitleBar>
<div class=CodeDisplayLanguage>
<div class=CopyCodeButton><a class=copyCode>Copy Code <pre class=libCScode id="ctl00_rs1_mainContentContainer_ctl13 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
[/code]
The <i>FindLastRow</i> function scans through the worksheet to find the first cell that doesnt have a value. A simple <i>While</i> 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 <i>r</i> 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 <i>LastRow</i> variable by adding the following line to the <i>UserForm_Initialize</i> event.
<div class=libCScode id="ctl00_rs1_mainContentContainer_ctl14_
<div class=CodeSnippetTitleBar>
<div class=CodeDisplayLanguage>
<div class=CopyCodeButton><a class=copyCode>Copy Code <pre class=libCScode id="ctl00_rs1_mainContentContainer_ctl14 LastRow = FindLastRow
[/code]
The <i>FindLastRow</i> function can also be used in the event associated with the Last button to update the <i>LastRow</i> variable as well as set the value for the <i>RowNumber</i> control.
<div class=libCScode id="ctl00_rs1_mainContentContainer_ctl15_
<div class=CodeSnippetTitleBar>
<div class=CodeDisplayLanguage>
<div class=CopyCodeButton><a class=copyCode>Copy Code <pre class=libCScode id="ctl00_rs1_mainContentContainer_ctl15 Private Sub CommandButton4_Click()
LastRow = FindLastRow - 1
RowNumber.Text = FormatNumber(LastRow, 0)
End Sub
[/code]
<h3 class=dtH1>Editing Data</h3>
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 arent saved in the worksheet. There are a lot of different techniques you can use, but heres 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 <i>Enabled</i> property to <i>False</i>. Then change the <i>Enabled</i> property to <i>True</i> once one of the values in the field changes. You can reduce the amount of work by creating two subroutines, one named <i>EnableSave</i> and one named <i>DisableSave</i>, which enable and disable the command buttons associated with Save and Cancel, respectively. Then, in the <i>Change</i> event associated with the text boxes that contain data, add a call to the <i>EnableSave</i> subroutine. This setting means that any change to the data will mark the entire form as <i>dirty</i>, 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 <i>DisableSave</i> routine. This call should be placed only after the data is loaded onto the form because its possible that the user might not have entered a valid row number and <i>GetData</i> might not actually reload any data.
The <i>PutData</i> routine found in the user form module (shown in the following listing) is similar to the <i>GetData</i> routine in that all the validations used to ensure that the value in <i>RowNumber</i> is valid are included. The main difference between the two routines is that the <i>GetData</i> routine copies information <i>from</i> the worksheet, whereas the <i>PutData</i> routine copies data <i>to</i> the worksheet.
<div class=libCScode id="ctl00_rs1_mainContentContainer_ctl16_
<div class=CodeSnippetTitleBar>
<div class=CodeDisplayLanguage>
<div class=CopyCodeButton><a class=copyCode>Copy Code <pre class=libCScode id="ctl00_rs1_mainContentContainer_ctl16 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
[/code]
The error checking isnt absolutely necessary, but it probably is a good idea just in case someone put an invalid value in the <i>RowNumber</i> text box, jumped to another application, and then came back. In that scenario, its 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, the <i>DisableSave</i> routine is called. This is necessary because the data on the user form now represents the same data stored on the worksheet.
<h3 class=dtH1>Adding Data</h3>
Pressing the Add button calls the <i>CommandButton7_Click</i> event, which displays the first blank row at the end of the worksheet. Because the <i>LastRow</i> variable points to this row, its merely a matter of setting the <i>Text</i> property of the <i>RowNumber</i> control to this value using code like this:
<div class=libCScode id="ctl00_rs1_mainContentContainer_ctl17_
<div class=CodeSnippetTitleBar>
<div class=CodeDisplayLanguage>
<div class=CopyCodeButton><a class=copyCode>Copy Code <pre class=libCScode id="ctl00_rs1_mainContentContainer_ctl17 Private Sub CommandButton7_Click()
RowNumber.Text = FormatNumber(LastRow, 0)
End Sub
[/code]
<h3 class=dtH1>Validating Data</h3>
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, its possible to enter an invalid date as part of the <i>DateAdded</i> field. Also, there are no checks to ensure that the <i>CustomerId</i> value is numeric. Finally, its 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 <i>KeyPress</i> 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 <i>CustomerId</i> control using this code:
<div class=libCScode id="ctl00_rs1_mainContentContainer_ctl18_
<div class=CodeSnippetTitleBar>
<div class=CodeDisplayLanguage>
<div class=CopyCodeButton><a class=copyCode>Copy Code <pre class=libCScode id="ctl00_rs1_mainContentContainer_ctl18 Private Sub CustomerId_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
If KeyAscii < Asc("0") Or KeyAscii > Asc("9") Then
KeyAscii = 0
End If
End Sub
[/code]
<blockquote class=dtBlock><b class=le>Tip: Defining Events </b>Double-clicking the <i>CustomerId</i> control on the user form will automatically take you to the <i>CustomerId_Change</i> event. If the event doesnt exist, it will automatically be created. If you want to handle a different event, simply choose the name of the event from the drop-down list at the top of the code window and the Visual Basic Editor will automatically create a blank event with the appropriate parameters.</blockquote>
Another approach involves using the <i>Exit</i> event. In the <i>Exit</i> 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:
<div class=libCScode id="ctl00_rs1_mainContentContainer_ctl19_
<div class=CodeSnippetTitleBar>
<div class=CodeDisplayLanguage>
<div class=CopyCodeButton><a class=copyCode>Copy Code <pre class=libCScode id="ctl00_rs1_mainContentContainer_ctl19 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
[/code]
One nice feature of the <i>Exit</i> event is that if you set the <i>Cancel</i> argument to <i>True</i>, 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 isnt an error, to reset any previous error conditions. This is handled by the <i>Else</i> clause.
The final technique used in this application prevents errors by substituting a combo box control in place of the text box control for <i>State</i>. 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 <i>MatchRequired</i> 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 <i>List</i>. Another way to ensure that only a valid value is selected is to set the <i>Style</i> property of the combo box control to <i>fmStyleDropDownList</i>, 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 <i>AddStates</i> routine is necessary to initialize the combo box control. This routine would typically be called from the user forms <i>Initialize</i> event.
<div class=libCScode id="ctl00_rs1_mainContentContainer_ctl20_
<div class=CodeSnippetTitleBar>
<div class=CodeDisplayLanguage>
<div class=CopyCodeButton><a class=copyCode>Copy Code <pre class=libCScode id="ctl00_rs1_mainContentContainer_ctl20 Private Sub AddStates()
State.AddItem "AK"
State.AddItem "AL"
State.AddItem "AR"
State.AddItem "AZ"
End Sub
[/code]
View the full article