Consulting

Results 1 to 7 of 7

Thread: Userform submit to Worsheet (Subscript out of range)

Hybrid View

Previous Post Previous Post   Next Post Next Post
  1. #1
    VBAX Newbie
    Joined
    Jan 2010
    Posts
    3
    Location

    Userform submit to Worsheet (Subscript out of range)

    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
    [VBA]
    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 "
    Me.txtfirstname.SetFocus
    Exit Sub
    End If
    If Me.txtlastname.Value = "" Then
    MsgBox "Please enter a Last Name.", vbExclamation, "Patients"
    Me.txtfirstname.SetFocus
    Exit Sub
    End If
    If Me.txtaddress.Value = "" Then
    MsgBox "Please enter an address.", vbExclamation, "Patients"
    Me.txtfirstname.SetFocus
    Exit Sub
    End If
    If Me.txtDOB.Value = "" Then
    MsgBox "Please enter a Date.", vbExclamation, "Patients"
    Me.txtfirstname.SetFocus
    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
    [/VBA]

    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.

  2. #2
    Moderator VBAX Wizard lucas's Avatar
    Joined
    Jun 2004
    Location
    Tulsa, Oklahoma
    Posts
    7,323
    Location
    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?
    Steve
    "Nearly all men can stand adversity, but if you want to test a man's character, give him power."
    -Abraham Lincoln

  3. #3
    VBAX Newbie
    Joined
    Jan 2010
    Posts
    3
    Location
    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.

  4. #4
    Moderator VBAX Wizard lucas's Avatar
    Joined
    Jun 2004
    Location
    Tulsa, Oklahoma
    Posts
    7,323
    Location
    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"
    Steve
    "Nearly all men can stand adversity, but if you want to test a man's character, give him power."
    -Abraham Lincoln

  5. #5
    Moderator VBAX Wizard lucas's Avatar
    Joined
    Jun 2004
    Location
    Tulsa, Oklahoma
    Posts
    7,323
    Location
    [VBA] RowCount = Worksheets("Sheet3").Range("A7").CurrentRegion.Rows.Count [/VBA]

    Is sheet3 named exactly as it is in your code?
    Steve
    "Nearly all men can stand adversity, but if you want to test a man's character, give him power."
    -Abraham Lincoln

  6. #6
    VBAX Newbie
    Joined
    Jan 2010
    Posts
    3
    Location
    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

  7. #7
    Moderator VBAX Wizard lucas's Avatar
    Joined
    Jun 2004
    Location
    Tulsa, Oklahoma
    Posts
    7,323
    Location
    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:

    [vba]RowCount = Worksheets("Sheet5").Range("A7").CurrentRegion.Rows.Count[/vba]

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

    See image.
    Steve
    "Nearly all men can stand adversity, but if you want to test a man's character, give him power."
    -Abraham Lincoln

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •