Excel VBA Tools from Andrew
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: run time error 9 subscript out of range

  1. #1
    Board Regular
    Join Date
    Feb 2018
    Posts
    150
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default run time error 9 subscript out of range

    Hi, i get a run time error 9 subscript out of range and when i debug it highlights in yellow my Uerform7.show can you help at all please to stop this, it happens when i copy data from another spreadsheet into my userform on the main one

  2. #2
    Board Regular davesexcel's Avatar
    Join Date
    Feb 2006
    Location
    Cochrane
    Posts
    437
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: run time error 9 subscript out of range

    Code:
    Uerform7.show
    That line if code is not the problem, the issue would be in the userform initialize code. If you are trying to populate controls from a worksheet or workbook that the code can't find.

  3. #3
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    9,135
    Post Thanks / Like
    Mentioned
    163 Post(s)
    Tagged
    10 Thread(s)

    Default Re: run time error 9 subscript out of range

    In the VBE Click Tools > Options > General > select Break in Class Module > OK.
    Try your code again & you should see where the problem is.
    - Posting guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes

    Running Office 2003 & 2013 on Win 7

  4. #4
    Board Regular
    Join Date
    Feb 2018
    Posts
    150
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: run time error 9 subscript out of range

    hi thanks for that i have found the error if you can help please it is highlighted in BOLD below the error, what i have found is that if i copy some data from in day lists onto a black excel document then copy that into my userform to find the data it comes up with the error even though the data is in in day lists. can you help please
    Code:
    Private Sub UserForm_Initialize()
    With Sheets("IN DAY LISTS")
        ComboBox2.RowSource = ""
        ComboBox2.List = .Range("D2:D" & .Range("D" & Rows.Count).End(xlUp).Row).Value
        ComboBox1.RowSource = ""
        ComboBox1.List = .Range("C2:C" & .Range("C" & Rows.Count).End(xlUp).Row).Value
        ComboBox3.RowSource = ""
        ComboBox3.List = .Range("G2:G" & .Range("G" & Rows.Count).End(xlUp).Row).Value
    End With
    End Sub

  5. #5
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    31,383
    Post Thanks / Like
    Mentioned
    14 Post(s)
    Tagged
    2 Thread(s)

    Default Re: run time error 9 subscript out of range

    There must be an issue with the sheet name. Make sure the sheet tab doesn't have leading or trailing spaces in its name for example.

  6. #6
    Board Regular
    Join Date
    Feb 2018
    Posts
    150
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: run time error 9 subscript out of range

    hi, its ok when i actualy copy data from the sheet name and put it in the userform, it is when i copy data from the sheet into a black sheet then paste into the userform it doesnt find it in the sheet then? i presume its trying to find from the blank?

  7. #7
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling, Scotland
    Posts
    73,540
    Post Thanks / Like
    Mentioned
    22 Post(s)
    Tagged
    0 Thread(s)

    Default Re: run time error 9 subscript out of range

    Do you have a sheet named 'IN DAY LISTS' in the workbook that's active when the code is run?
    If posting code please use code tags.

  8. #8
    Board Regular
    Join Date
    Feb 2018
    Posts
    150
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: run time error 9 subscript out of range

    yes

  9. #9
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling, Scotland
    Posts
    73,540
    Post Thanks / Like
    Mentioned
    22 Post(s)
    Tagged
    0 Thread(s)

    Default Re: run time error 9 subscript out of range

    There are definitely no other workbooks open?
    If posting code please use code tags.

  10. #10
    Board Regular
    Join Date
    Feb 2018
    Posts
    150
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: run time error 9 subscript out of range

    hi yes there is what happening is that people open a blank excel sheet to copy the data from the IN DAY LISTS SHEETS and then copy for example a job number to enter into the userform to find the data and that is when the error comes up, for some reason its not working even though the data to find is still located in the IN DAY LISTS

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

DMCA.com