View Full Version : Userform submit to Worsheet (Subscript out of range)

01-31-2010, 09:48 AM
Ive been assigned with a assignment for Uni to create a system in excel.

Im currently stuck on creating the user form using the submit button. Ive used a tutorial to create the form and the code, but it doesnt work i get a subscript out of range error 9)

Here is what i have

Private Sub addpatient_Click()

Dim RowCount As Long
Dim ctl As Control
' Check user input
If Me.txtfirstname.Value = "" Then
MsgBox "Please enter a First Name.", vbExclamation, "Patients "
Exit Sub
End If
If Me.txtlastname.Value = "" Then
MsgBox "Please enter a Last Name.", vbExclamation, "Patients"
Exit Sub
End If
If Me.txtaddress.Value = "" Then
MsgBox "Please enter an address.", vbExclamation, "Patients"
Exit Sub
End If
If Me.txtDOB.Value = "" Then
MsgBox "Please enter a Date.", vbExclamation, "Patients"
Exit Sub
End If

' Write data to worksheet
RowCount = Worksheets("Sheet3").Range("A7").CurrentRegion.Rows.Count
With Worksheets("Sheet3").Range("A7")
.Offset(RowCount, 0).Value = Me.txtfirstname.Value
.Offset(RowCount, 1).Value = Me.txtlastname.Value
.Offset(RowCount, 2).Value = Me.txtaddress.Value
.Offset(RowCount, 3).Value = DateValue(Me.txtDOB.Value)
.Offset(RowCount, 4).Value = Me.txtprevnotes.Value

End With
' Clear the form
For Each ctl In Me.Controls
If TypeName(ctl) = "TextBox" Or TypeName(ctl) = "ComboBox" Then
ctl.Value = ""
ElseIf TypeName(ctl) = "CheckBox" Then
ctl.Value = False
End If
Next ctl
End Sub

Any ideas, it highlights the "Sheet 3" bit but ive changed it the other name too with the same error.

I also have another error on another form saying Method or data member not found

No idea what they mean, ive searched all of the internet with no help, im hoping for some help here.

Thanks in advance

Edit: VBA tags added to code. Poster, use the vba button to surround your code instead of the quote or code button.

01-31-2010, 09:52 AM
It would help if you could post your workbook so we don't have to re-create the userform, etc. just to help you.

On which line do you recieve the subscript out of range error? Does sheet 3 exist?

01-31-2010, 09:59 AM
Sorry, how would i go about uploading it?

Yes sheet 3 exists, and even when i change it to "Patients" which is the name ive called it, i still get an error. I

I get an error when i press the button after all information is entered.

01-31-2010, 10:04 AM
click the post reply button at the bottom left of the last post.

type your message and scroll down until you see a button that says "manage attachments"

01-31-2010, 10:05 AM
RowCount = Worksheets("Sheet3").Range("A7").CurrentRegion.Rows.Count

Is sheet3 named exactly as it is in your code?

01-31-2010, 10:13 AM
Ive just uploaded the file.

I have changed the name to "Patients" and even tried that and it doesnt work.

"Sheet3" was named correctly, im pretty it was.

Thanks for the help

01-31-2010, 10:29 AM
See the picture. Method or data member not found:
There is no textbox named txtfirstname on the userform. 4 instances.

I don't have 2007 but in 2003 you should go to view-properties in the vbe and select the textboxes to see what they are named.

subscript out of range. There is no worksheet5 in the workbook:

RowCount = Worksheets("Sheet5").Range("A7").CurrentRegion.Rows.Count

All this code comes from the click event for the Appointform userform.

See image.