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.

32 comments
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.

minkewhale
minkewhale

So is that in the "Visual Basic" Window or in the regular Excel workbook. If it's in the visual basic, is that any different than the control tool box that was being used for from the beginnign?

TomDSmith
TomDSmith

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

lnn5432
lnn5432

one you close the worksheet after entering some data, is there a way to get back to the working data entry box??? Thanks in advance

ssharkins
ssharkins

I apologize for the error in the posted code.

Tech
Tech

Sorry I see the problem, the "." is missing from the original script.

Tech
Tech

Same Problem here, not a VB guru so cant debug it easily, anyone know whats up with this code?

Corbybhoy
Corbybhoy

If you are apologetic why not correct it so it works for new users?

hulyalkar
hulyalkar

The best way to avoid mistakes is to follow "Poka Yoke" (Japanese Word for mistake proofing) used by quality professionals in any process. I suggest attaching a test file with features explained. This will ensure that written code works. This will save lot of wo/manhours globally.

Diskb0x
Diskb0x

ok I put the "." in but now get "runtime error 13, type mismatch" for line Application.WorksheetFunction.CountA (Range("A:A")) + 1 Any ideas guys?

uberg33k50
uberg33k50

My final code looks like this and it works. Private Sub OKButton_Click() 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 End Sub

uberg33k50
uberg33k50

Mark check everything else...commenting out that line made it work for me. I get both the name and the level. Thanks sadamson.

ubeg41
ubeg41

I have tried following your suggestions and am unsure exactly what the lines of code should look like. please repeat total line of code for me. Thanks

Diskb0x
Diskb0x

Cheers sadamson. This sort of worked as it now puts the name into Excel but doesn't record their education level. Any other tips?

roclmbr
roclmbr

I commented out that particular line of code, and the program seemed to work OK.

Editor's Picks