+ Reply to Thread
Results 1 to 3 of 3

Subscript out of range userform error

  1. #1
    Registered User
    Join Date
    08-21-2013
    Location
    Atlanta, Georgia
    MS-Off Ver
    Excel 2007
    Posts
    2

    Subscript out of range userform error

    This is my first time trying to put together a UserForm and it is getting to be a little more complicated than I thought. Everytime I run this, the userform pops up and everything works fine until I hit the "Sign Up" button which causes a "Subscript out of range" error. It started after I added the section for writing the data to the spreadsheet. I don't know what I'm doing wrong. HELP!



    Private Sub SignUpButton_Click()

    Dim RowCount As Long
    Dim ctl As Control

    'Check user input
    If Me.WWIDEntry.Value = "" Then
    MsgBox "Please enter a WorldWide ID.", vbExclamation, "Incomplete Form"
    Me.WWIDEntry.SetFocus
    Exit Sub
    End If
    If Me.FirstNameEntry.Value = "" Then
    MsgBox "Please enter a First Name.", vbExclamation, "Incomplete Form"
    Me.FirstNameEntry.SetFocus
    Exit Sub
    End If
    If Me.LastNameEntry.Value = "" Then
    MsgBox "Please enter a Last Name.", vbExclamation, "Incomplete Form"
    Me.LastNameEntry.SetFocus
    Exit Sub
    End If
    If Me.DepartmentEntry.Value = "" Then
    MsgBox "Please enter a Department.", vbExclamation, "Incomplete Form"
    Me.DepartmentEntry.SetFocus
    Exit Sub
    End If

    'Write data to worksheet
    RowCount = Worksheets("Sign Up").Range("A1").CurrentRegion.Rows.Count
    With Worksheets("Sign Up").Range("A1")
    .Offset(RowCount, 1).Value = Me.WWIDEntry.Value
    .Offset(RowCount, 2).Value = Me.LastNameEntry.Value
    .Offset(RowCount, 3).Value = Me.FirstNameEntry.Value
    .Offset(RowCount, 4).Value = Me.NicknameEntry.Value
    .Offset(RowCount, 5).Value = Me.DepartmentEntry.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

  2. #2
    Forum Expert Solus Rankin's Avatar
    Join Date
    05-24-2013
    Location
    Hollywood, CA
    MS-Off Ver
    Win7 Office 2010 VS Express 2012
    Posts
    2,655

    Re: Subscript out of range userform error

    Please use code tags when posting code. To correct this click 'Edit Post' at the bottom of your post. Highlight the code and press the # in the tool bar.

    If I had to guess, I'd say you either have a spelling issue with your sheet name (check that your spaces are correct, sometimes a space sneaks in at the end of the sheet name and throws it off) or you RowCount variable = 0

    Try:

    Please Login or Register  to view this content.
    Thanks,
    Solus


    Please remember the following:

    1. Use [code] code tags [/code]. It keeps posts clean, easy-to-read, and maintains VBA formatting.
    Highlight the code in your post and press the # button in the toolbar.
    2. Show appreciation to those who have helped you by clicking below their posts.
    3. If you are happy with a solution to your problem, mark the thread as [SOLVED] using the tools at the top.

    "Slow is smooth, smooth is fast."

  3. #3
    Forum Expert Solus Rankin's Avatar
    Join Date
    05-24-2013
    Location
    Hollywood, CA
    MS-Off Ver
    Win7 Office 2010 VS Express 2012
    Posts
    2,655

    Re: Subscript out of range userform error

    Another thought... you don't have to use the offset method, you can write directly to the cells by:
    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Defining Array - Runtime error 9, Subscript out of range error
    By MaartenW in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 02-03-2012, 07:32 AM
  2. Run-time error '9': Subscript out of range when trying to open a UserForm
    By Kungfauxn00b in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-22-2010, 02:23 PM
  3. Userform submit to Worsheet (Subscript out of range)
    By Rampage in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 02-05-2010, 10:26 AM
  4. Out of Subscript error while opening a Userform
    By shivboy in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 06-22-2006, 07:07 AM
  5. Subscript out of range error - save copy error
    By bg18461 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-13-2006, 11:53 AM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1