Microsoft

Create an Excel data entry form that includes check boxes

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.

Help users increase productivity by automatically signing up for TechRepublic's free Microsoft Office Suite newsletter, featuring Word, Excel, and Access tips, delivered each Wednesday.

33 comments
LaymanGirl
LaymanGirl

Hi,


Great tutorial! I'm completely new at this and nailed my first input sheet and then some... I have created a multipage input sheet for auditing with 100+ fields (textboxes; option buttons & comboboxes). 


1) BUT with so many fields I need code for my control OK button to "complete all fields" before it loads the data into my database.


2) I would also like if my control OK button can automatically clear all data from the input sheet so that I can start on a clean slate with each input. 


3) Pushing my luck now I would also like to also create a review control button that gives me the option to displays all the data before loading it onto my database. 


Any help would be appreciated!



ch3z
ch3z

I had a similar issue with these two lines:

NextRow =Application WorksheetFunction.CountA(Range("A:B"))+1

Application.WorksheetFunction.CountA(Range("A:A")) +1

So I removed the second line completely and changed the first line to:

NextRow =Application.WorksheetFunction.CountA(Range("A:B"))+1

This stopped the "runtime error 13, type mismatch"

And adds a new line to the work sheet.

The issue I am having is that it wont enter the Education details in the next column.  Can anyone help me??

Thank you

adbarmukh
adbarmukh

I have tried the user form but unsuccesfully encountered error message : Run time error 424 object required please help

bhutini
bhutini

the excel application tool bar. ok.... right next to "help".

baldrics
baldrics

Right-click a toolbar and select Control Toolbox. what toolbar how??? more detail please, I'm a novice

Berz3rk
Berz3rk

Create a pick list instead. It's much easier. In another area of the sheet (I usually put this on another tab,) enter the values you need. Highlight the values then in the top left area in the sheet where the selected cell displays, enter the name of your value list then press enter. Go to the area on your sheet where you want to use the pick list then select Data tab then select on the Data Tools group select Data validation. On the Settings Tab, select allow "List" then enter =(name of your list) and save that. I use this all the time and it's very easy. You can drag this to multiple cells.

weeyin
weeyin

Wow just what I needed. Good info on a data entry form. Then it all went downhill. When I get a great article from Tech Republic I usually copy and paste the article and the normally beneficial extra comments into Word to keep, reuse and pass around my colleagues. There were so many issues and tweaks here that i sadly 'binned' my work on this. I have to give TR 10/10 though for having the bottle to apologise and my request is for a tweaked and working fresh article content that doesn't mean I have to go through and amend and include everyone elses necessary modifications to get the form to work In every basket of golden nuggets is the occassional stone and this was the stone. Now back to the nuggets!!!!

amy.mel.m
amy.mel.m

im just finish designing the form.yeah i made it.so, i try to fill in the form and click the OK button but it result in error as shown below... Private Sub OKButton_Click() anyone could help me on this?

mlucca1
mlucca1

is a great tip, but for me that I am fairly new, some of the instructions are not to clear. because I do not know where the buttons or for eaxample:"click on the label control and drag it to the form". Where is the label control?? I got lost, it'll be useful if pictures showing where to go are included. Thank you

smahnaqvi
smahnaqvi

i need almost the same thing but data is entered from a form (not user form like used in this example) in sheet1 with "new record", "save record" and "cancle" button and data is entered in sheet2 with some calculated values in adjacent columns.

N1k100
N1k100

This code appears to have a problem, not that I would have any idea, although I did try it! I just read the comments. I would so love this code to work and if someone could rewrite it I would really appreciate that. And if it worked allowing more than one check box to be ticked that would be a dream come true. I am a complete novice at VB Editor but would love to learn. Thanks.

robert.dadomo
robert.dadomo

Hi, Just found this link and was working through it. A few questions: What version of Office is this used for? From point 30, I press Alt + F11 and I can't find where to go from there. It brings up the excel worksheet. I right click a toolbar and no 'control toolbox' appears. Can someone help me? Thanks

jain747
jain747

After NextRow =Application WorksheetFunction.CountA(Range("A:A"))+1, I added this line: nextrow = nextrow + 1. Noticed that after adding a 2nd name and education level, the 1st name and education level was being replaced. Adding the line above the 2nd name and education level will be added to the next row. also, I think radio buttons should be used instead of checkboxes as only 1 education level will be recorded into the 2nd column

Nodisalsi
Nodisalsi

This is the first time I've ever coded VB script for Excel since the good old days of Windows3.11! This had errors, but I managed to debug this myself by removing a superfluous line and realising I'd forgotten to enclosed quotes. But there's just one thing... When giving instructions by keyboard shortcuts - it helps to give the menu/toolbox equivalents and state keyboard shortcuts in (brackets). While following these instructions I was not aware what Alt-F11 or F4 etc were supposed to do.

mw00110011
mw00110011

First; Really nice to see application coding introduced. Overall, a nice project for demonstration purposes. Issues; (1) The article code didn't Dim the NextRow variable - not a deadly sin, just good practice. (2) Haven't looked back to prior versions of Excel VB toolbox, but, the "Option" control may be a better choice over the "Checkbox" control, since code only enters the highest value no matter how many check boxes are marked. (3) Looks like the second, "Application.WorksheetFunction.CountA(Range("A:A")) +1" line was an attempt to correct the line above, adding the period in place of the space. Only problem is it didn't assign the value to NextRow. (4) UserForm1 fields need some basic validation as it is possible to enter an Education level without a name and the next entry from the user form will fall into the same place, possible overwriting whatever level was placed in the worksheet. (5) Would also be a good place to discuss the typical and necessary Macro Security settings and how a user may interpret the initial warning dialog that appears when the file is loaded with Medium security. Overall, a fun and interesting demonstration.

kohl
kohl

Please check your final article by following -- cutting and pasting where appropriate -- your own instructions to be sure the instructions, as you give them, actually work. Otherwise many man/woman hours are wasted. Remember you are addressing a large audience and any error you make in your instructions multiplies many times over, particularly among those who need your tip the most. Thanks.

Tech
Tech

There is a problem with this line of code: "NextRow =Application WorksheetFunction.CountA(Range("A:A"))+1" It produces the error Compline Error: Expected : end of statment

rapell
rapell

I have a problem with this line: Application.WorksheetFunction.CountA(Range("A:A")) +1 mismatch type 13. Anyone run the sheet? Nice idea otherwise.

Editor's Picks