Create User Forms

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
UserForm	UserForm	Name	frmCustInfo
		Caption	Customer Information Form
Business Name	Text Box	Name	txtBName
Business Name Label	Label	Name	lblBusiness_Name
		Caption	Business Name
Customer Contact Name	Text Box	Name	txtCustContact
Customer Contact Name Label	Label	Name	lblCust_Contact
		Caption	Customer Contact Name
Street	Text Box	Name	txtStreet
Street Label	Label	Name	lblStreet
		Caption	Street
City	Text Box	Name	txtCity
City Label	Label	Name	lblCity
		Caption	City
State	Combo Box	Name	cboState
State Label	Label	Name	lblState
		Caption	State
Zip	Text Box	Name	txtZip
Zip Label	Label	Name	lblZip
		Caption	Zip
Phone	Text Box	Name	txtPhone
Phone Label	Label	Name	lblPhone
		Caption	Phone
Fax	Text Box	Name	txtFax
Fax Label	Label	Name	lblFax
		Caption	Fax
Email	Text Box	Name	txtEmail
Email Label	Label	Name	lblEmail
		Caption	Email
Active	Check Box	Name	chkActive
Past Due	Frame	Name	frPastDue
		Caption	Past Due
		Enabled	FALSE
Not Overdue	Option Button	Name	opNo
		Caption	Not Overdue
		Enabled	FALSE
Less Than 90 Days	Option Button	Name	opL90
		Caption	Less Than 90 Days
		Enabled	FALSE
Over 90 Days	Option Button	Name	op90
		Caption	Over 90 Days
		Enabled	FALSE
Over 180 Days	Option Button	Name	op180
		Caption	Over 180 Days
		Enabled	FALSE
Over 360 Days	Option Button	Name	op360
		Caption	Over 360 Days
		Enabled	FALSE
OK	Command Button	Name	cmdOK
		Caption	OK
		Default	TRUE
Cancel	Command Button	Name	cmdCancel
		Caption	Cancel
		Cancel	TRUE
Reset Form	Command Button	Name	cmdReset
		Caption	Reset Form

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()

End Sub

Enter the following in between those two lines: Unload Me
You code should look like this:
Private Sub cmdCancel_Click()

Unload Me

End Sub

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()

Call UserForm_Initialize

End Sub

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.

Download a full working example of the customer contact user form we just created.