Have you ever had a spreadsheet, where you wanted users to be able to enter information in a form, and have it populate the next available row in a given set of columns? It’s easier than you thought, and will make entering data more efficient and accurate.
The example below will use most of the common UserForm tools to give you a feel for how they work. You can later apply this example to your own custom forms.
This example will create a form to ask users to input information about your customers, and upon successfully filling out the form will populate a spreadsheet with the appropriate information. Note: There’s a full working example of this UserForm available to download by clicking here.
The first step is to set up your spreadsheet to accept the data you’ll ask your users to populate. (Note: don’t type the quotes shown below).
- Open a new workbook in Microsoft Excel. In “Sheet1” change the sheet’s name to “Customer Data”.
- In cell A1, type “Business Name”.
- In cell B1, type “Customer Contact Name”.
- In cell C1, type “Street Address”.
- In cell D1, type “City”.
- In cell E1, type “State”.
- In cell F1, type “Zip”.
- In cell G1, type “Phone”.
- In cell H1, type “Fax”.
- In cell I1, type “Email”.
- In cell J1, type “Active”.
- In cell K1, type “Past Due”.
The second step is to create your UserForm and add the controls that users will ultimately populate with data.
- Open the Visual Basic Editor (Alt+F11).
- While in the Visual Basic Editor, insert a new UserForm (Insert | UserForm). Also make sure the Toolbox menu appears when you do this. If not, click View | Toolbox.
- To insert a control, select it from the Toolbox and drag it to where you want it in the form. Note: you can drag the edges of the UserForm and the controls to make them bigger/smaller, depending on how much data you’ll be entering.
You’ll want to enter the following controls below into your UserForm.
- 8 TextBoxes. 1 for each of the following: Business Name; Customer Contact Name; Street Address; City; Zip; Phone; Fax; Email. Note: You may also want to include a Label for each textbox to visually tell the user what information should be entered into each box.
- 1 ComboBox for the State field.
- 1 CheckBox. This is to check if the customer is an active customer.
- 1 Frame. Within this frame put five OptionButtons. This is where you’ll indicate if they’re past due, or not and if so, how long past due. This will populate column K above.
- Finally, 3 CommandButtons. One that will say “OK”, another that will say “Cancel” and a third that will say “Reset Form”.
Use the following configuration settings for each of the controls in your form.
|Data Element||Control||Property||Property Setting|
|Caption||Customer Information Form|
|Business Name||Text Box||Name||txtBName|
|Business Name Label||Label||Name||lblBusiness_Name|
|Customer Contact Name||Text Box||Name||txtCustContact|
|Customer Contact Name Label||Label||Name||lblCust_Contact|
|Caption||Customer Contact Name|
|Not Overdue||Option Button||Name||opNo|
|Less Than 90 Days||Option Button||Name||opL90|
|Caption||Less Than 90 Days|
|Over 90 Days||Option Button||Name||op90|
|Caption||Over 90 Days|
|Over 180 Days||Option Button||Name||op180|
|Caption||Over 180 Days|
|Over 360 Days||Option Button||Name||op360|
|Caption||Over 360 Days|
|Reset Form||Command Button||Name||cmdReset|
Now that you’ve created your form in Visual Basic, you need to initialize it.
- View the form’s code, by clicking View | Code or F7.
- You’ll use the names of each form control in this process, so make sure you know what each is.
- Copy and paste the following code into your form’s code.
- This will clear any old data out of your UserForm and get it set for editing, by setting default values. If, for example the majority of your customers are from Texas, you could change the value for cboState.Value = “” to cboState.Value = “TX”. This saves the end user one additional step in the process. Also, for purposes of this example, we’ll assume that the majority of our customers are not past due with payments, so we’ll set opNo = True to set the Past Due option button of Not Overdue to be the default option.
Now, go back to the UserForm, by clicking frmCustInfo in the left pane. Now double click on your Cancel button. This brings you back to the code editor. You’ll see the following code:
Private Sub cmdCancel_Click()
Enter the following in between those two lines: Unload Me
You code should look like this:
Private Sub cmdCancel_Click()
Now do the same for the Reset Form button. This time, in between Private Sub cmdReset_Click() and End Sub, enter Call UserForm_Initialize, so your code should look like this:
Private Sub cmdReset_Click()
Next, we’ll code the OK button, so we’ll follow the same process, but this time the code that will go in between Private Sub cmdOK_Click() and End Sub is:
Since we set the past due options “Enabled” property to False, we need to be able to tell the form when to set it to True. We’ll assume that we only want the past due options to be editable if the customer’s account is active. We can do so with the following code:
Finally, we need to be able to let users bring up this form from within Excel. We can do this with a simple macro that looks like this:
This macro needs to be placed either in a module, or in a Microsoft Excel Object (This Workbook, Sheet1, Sheet2, etc.). It should not be placed in with the rest of your form’s code, as you won’t be able to run the code from Excel.