Discussion on:

Create an Excel data entry form that includes check boxes

I have a problem with this line:
Application.WorksheetFunction.CountA(Range("A:A")) +1

mismatch type 13. Anyone run the sheet? Nice idea otherwise.

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

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

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

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?

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

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

Any other tips?

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

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

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.

I apologize for the error in the posted code.

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.

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.

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.

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

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

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.

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.

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

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?

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!!!!

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.

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

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

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