run time error 9 subscript out of range

Patriot2879

Well-known Member
Joined
Feb 1, 2018
Messages
1,226
Office Version
  1. 2010
Platform
  1. Windows
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
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
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.
 
Upvote 0
In the VBE Click Tools > Options > General > select Break in Class Module > OK.
Try your code again & you should see where the problem is.
 
Upvote 0
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()
[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
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
Do you have a sheet named 'IN DAY LISTS' in the workbook that's active when the code is run?
 
Upvote 0
There are definitely no other workbooks open?
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,212,927
Messages
6,110,731
Members
448,294
Latest member
jmjmjmjmjmjm

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top