Issues with Userforms

[junepeace]

Hi guys,

I'm pretty new to this. I'm trying to set up a way for school groups to enter their students information for programs we run, and I'm having trouble getting the Userform to enter the data into an excel sheet.

I'm currently getting Runtime Error 9, Subscript out of Range for the following section of code;

Dim lrTestform As Long 'enter data to sheet
lrTestform = Sheets("Testform").Range("B" & Rows.Count).End(xlUp).Row
Sheets("Testform").Cells(lrTestform + 1, "B").Value = Firstname.Text
Sheets(“Testform”).Cells(lrTestform + 1, “C”).Value = Me.Lastname.Text
Sheets(“Testform”).Cells(lrTestform + 1, “D”).Value = Me.age.Value
Sheets(“Testform”).Cells(lrTestform + 1, “E”).Value = Me.Gender.Value
Sheets(“Testform”).Cells(lrTestform + 1, “F”).Value = Me.Grade.Value
Sheets(“Testform”).Cells(lrTestform + 1, “G”).Value = Me.Discepline.Value
Sheets(“Testform”).Cells(lrTestform + 1, “H”).Value = Me.shoesize.Value
Sheets(“Testform”).Cells(lrTestform + 1, “I”).Value = Me.HT.Value
Sheets(“Testform”).Cells(lrTestform + 1, “J”).Value = Me.weight.Value
Sheets(“Testform”).Cells(lrTestform + 1, “K”).Value = Me.Skier.Value
Sheets(“Testform”).Cells(lrTestform + 1, “L”).Value = Me.Ability.Value
Sheets(“Testform”).Cells(lrTestform + 1, “M”).Value = Me.Lessons.Value
Sheets(“Testform”).Cells(lrTestform + 1, “N”).Value = Me.Rentals.Value
Sheets(“Testform”).Cells(lrTestform + 1, “O”).Value = Me.LiftPass.Value
Sheets(“Testform”).Cells(lrTestform + 1, “P”).Value = Me.Helmet.Value

Help?

I'm really new to this, and I'm trail and erroring things.

[nigelog]

Does your userform have an item called Lastname??

You should wrap code to make it easier to read using [ code] and [ /code] without the first space

[Arkadi]

Look at your quotes around the column letters... the 2 lines that succeeded have a proper double quote around them, the lines starting with your error have some other kind of quote symbol. (AND sheet name also has the quotes issue)

Welcome to the forum by the way, but please do look at the forum rules please?

Your post does not comply with Rule 2 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found at http://www.excelforum.com/forum-rule...rum-rules.html

[junepeace]

OK! Changing the quotes solved the Error (damn french keyboard), but it's still not entering info into the sheet. Here's all the code I have.

Please Login or Register  to view this content.

[Arkadi]

Are the controls populated? (I assume so)... One thing to check is what is lrTestform at the time it writes to the sheet? Any chance there is data somewhere way down that causes the last row to come up as a high number?

Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[Fluff13]

Whilst this won't correct the problem of data not being entered into the sheets.
This line

Please Login or Register  to view this content.

should be

Please Login or Register  to view this content.

[junepeace]

You genius! The data was hiding below my list of #s. But this brings up another issue;

I would like to have a identification number beside the names that is either auto filled out, or that the information is filled out beside the number.

This is so if there are changes they can tell me "person #45 needs x".

Ideas?

Also, I'd like to prevent data from being saved to the sheet if it is incomplete. Should i use another IF function or something differnt?

[Arkadi]

@Fluff13 good call! I missed that entirely.

@junepeace, I assume you mean the values in column A should auto-fill?

Maybe:
After the line

Please Login or Register  to view this content.

Add:

Please Login or Register  to view this content.

[junepeace]

That works great for auto-populating! Thank you!

Userform is written as Dataform because that's what I've named the form, I haven't had any issues with that so far, so unless you know of a reason I should change it I'm not going to change that.

Also, I'd like to prevent data from being saved to the sheet if it is incomplete. Should i use another IF function or something differnt?

[Fluff13]

so unless you know of a reason I should change it I'm not going to change that.

One good reason is that it doesn't run. A userform initialise event HAS to be

Please Login or Register  to view this content.

Regardless of the name of the userform.
As it happens you can simply delete it, as it's not doing anything.

[Arkadi]

junepeace... you have no issues because you have named ranges as the source of the data for the dropdowns... but the sub called Dataform_Initialize is not actually running... not saying you have to change it, just letting you know it doesn't actually do anything. Up to you what you choose to do with that.

As for incomplete data, you have all kind of ifs set up that bring up a message box. I would suggest either a boolean variable set to true at the start, and then set it to false on every blank item... then "If variable = False Exit Sub" at the end.

OR Since you are setting focus to each item, after the .SetFocus you could just add "Exit Sub" which would stop the code, user can fill in the box with focus and try again. If they get past all, then it is good to proceed.

[Arkadi]

Thanks for the rep junepeace, do I assume we have solved your issue? If so, please take a moment to mark the thread as solved if you would be so kind? Thanks

[Fluff13]

Ditto what Arkadi said

[junepeace]

Hi, almost done, I moved things around and it's the same runtime 9 error

Please Login or Register  to view this content.

[Arkadi]

Sorry no PC at the moment. Reply soon, though Fluff13 may well beat me to it

[torachan]

I have been following this thread with interest.
Is there missing communications ???
I can see no Sheet4 to be able to comment on what is causing runtime error
torachan

[torachan]

I have just pasted your code in and added a Sheet4, it works
I then deleted Sheet4 and get runtime error 9.
Conclusion:- you have no Sheet4.
torachan.

[bakerman2]

Assuming there is a Sheet4 like torachan says.

Please Login or Register  to view this content.

[Fluff13]

If Sheet4 is the CodeName rather than the sheet name, try

Please Login or Register  to view this content.

[junepeace]

Hi Guys!

I'm back!

Thanks for all your help, but I'm not done yet!

I'm having issues with nothing working when the file is opened as an email attachment. Help?

[Arkadi]

That could be a security setting within excel? Usually on my machine, if I open a macro-enabled excel file, I get prompted if I want to enable macros, but first have to click "Enable Editing" at the top of the window.

If the attachment is first saved and THEN opened, does it work?