run time error 9 subscript out of range

[Patriot2879]

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

 

[davesexcel]

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.

 

[Fluff]

In the VBE Click Tools > Options > General > select Break in Class Module > OK.
Try your code again & you should see where the problem is.

 

[Patriot2879]

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

Private Sub UserForm_Initialize()
[B]With Sheets("IN DAY LISTS")[/B]
    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

 

[RoryA]

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.

 

[Patriot2879]

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?

 

[Norie]

Do you have a sheet named 'IN DAY LISTS' in the workbook that's active when the code is run?

 

[Patriot2879]

yes

 

[Norie]

There are definitely no other workbooks open?

 

[Patriot2879]

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