Create an Excel data entry form that includes check boxes

By Mary Ann Richardson

Takeaway: You can keep a lot of information on an Excel data entry form, but you can make it easier for your users — and yourself — to create tables by adding check boxes. Here’s how to set up check boxes that you can later translate into a report you want.

Excel provides a simple data form to enter data into an Excel database. The form only only contains text box controls, which can be cumbersome when entering certain types of data.
For example, you want to create a table that lists the educational level of your latest batch of job applicants. Rather than typing high school, college, or graduate school, you can create a form that allows you simply to check a box to indicate the education level. Follow these steps:
1. Open a blank worksheet of your workbook.
2. Press [Alt][F11].
3. Go to Insert | User Form.
4. If necessary, press [F4] to display Userform1 properties.
5. Click in the Caption property box and change the name to Get Name and Education.
6. Click the Label control and drag it to the form.
7. Click in the Label’s Caption property box and type Name:.
8. Click the Text Box control in the Control toolbox and drag it to the form. Locate it to the right of the Label control.
9. Click in the Name property of the Text Box control and type TextName.
10. Click and drag the Frame control from the Control toolbox to the form and locate it below the text box.
11. Click in the Caption property of the Frame control and type Education Level:.
12. Click and drag the Check box control from the Control toolbox to the form and locate it within the frame.
13. Change the Check box Name property to OptionHS.
14. Change the Caption property of the Check box control to High School.
15. Click and drag the Check box control to the form and position it below the first check box.
16. Change the Check box Name property to OptionCollege.
17. Change the Caption property of the Check box control to College.
18. Click and drag the Check box control from the Control toolbox to the form and locate it below the second check box.
19. Change the Check box Name property to OptionGrad.
20. Change the Caption property to Graduate School.
21. Click and drag the right border of the form to increase the form’s size.
22. Click the Command Button control in the Control toolbox, drag it to the form, and locate it next to the TextName control.
23. Change the Caption property of the Command button to OK.
24. Change the Name property to OKButton.
25. Click in the Default Property box and select True.
26. Click and drag the Command Button control to the form from the Control toolbox and locate it below the OK button.
27. Change the Caption property to Cancel.
28. Change the Name property to CancelButton.
29. Click in the Cancel property box and select True.
30. Press [Alt][F11].
31. Right-click a toolbar and select Control Toolbox.
32. Click the Command tool in the Control Toolbox.
33. Click and drag to create the command button in your worksheet.
34. Right-click the Command button, point to Object, and then select Edit.
35. Select the default name and change it to Data Entry.
36. Click outside the button to deselect it and then double-click the button.
37. Enter Userform1.Show at the prompt.
38. Double-click Userform1 in the VBA Project Window. (To open the form so you can add code to the rest of the form objects. Double-click the name of the form — Userform1 — listed under the Forms folder in the VBA Project window.)
39. Double-click the Cancel button.
40. Enter Unload UserForm1 at the prompt.
41. Press [Shift][F7].
42. Double-click the OK button.
43. Enter the following code at the prompt:
Sheets("Sheet1").Activate
NextRow =Application WorksheetFunction.CountA(Range("A:A"))+1
Application.WorksheetFunction.CountA(Range("A:A")) +1
Cells(NextRow, 1)=TextName.Text
If OptionHS Then Cells(NextRow, 2) ="High School"
If OptionCollege Then Cells(NextRow, 2) ="College"
If OptionGrad Then Cells(NextRow, 2)="Grad School"
TextName.Text =""
TextName.SetFocus
44. Press [Alt]Q.
45. Click the Exit Design Mode button in the Control toolbox.
To enter the data into the blank worksheet, press the Data Entry button, fill in the name, click the Education Level, and then click OK. The data will automatically be entered in columns A and B. When you finish entering data, click Cancel to close the form.

Miss a tip?

Check out the Microsoft Excel archive, and catch up on our most recent Excel tips.