VBA Error Message Run-Time Error 1004

nuttycow13

New Member
Joined
Feb 6, 2012
Messages
47
I have written vba script but keep getting this error message:

Run-Time error '1004': Method 'Ranger' of Object'_Worksheet' Failed.

Can anyone tell me what's wrong with this code:


Private Sub UserForm_Initialize()
Dim Site As Range
Dim Location As Range
Dim Printer_Type As Range
Dim ws As Worksheet
Set ws = Worksheets("LookUp USSD")

For Each Site In ws.Range("Site")
With Me.Sitebox
.AddItem Site.Value
End With
Next Site

For Each Location In ws.Range("location")
With Me.Locationbox
.AddItem Location.Value
End With
Next Location

For Each Printer_Type In ws.Range("Printer_Type")
With Me.Printer_Type_box
.AddItem Printer_Type.Value
End With
Next Printer_Type

End Sub

Site, Location and Printer_Type are all combobox's, i think the error is the line i have made bold but could be wrong

if anyone could help me that would be brilliant as its really starting to bug me.

Thank you in advance.
 
Last edited:

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
I have written vba script but keep getting this error message:

Run-Time error '1004': Method 'Ranger' of Object'_Worksheet' Failed.


For Each Site In ws.Range("Site")
With Me.Sitebox
.AddItem Site.Value
End With
Next Site

For Each Location In ws.Range("location")
With Me.Locationbox
.AddItem Location.Value
End With
Next Location



Site, Location and Printer_Type are all combobox's, i think the error is the line i have made bold but could be wrong
The code would want to see a named range call "Site", similarly for "Location", do they exist as ranges?
 
Upvote 0
thanks for for response p45cal.

I have set site as a range in the begging of the code:

Private Sub UserForm_Initialize()
Dim Site As Range
Dim location As Range
Dim Printer_Type As Range
Dim ws As Worksheet
Set ws = Worksheets("LookUp USSD").Select

it seem to bum out at the Set ws = Worksheets("LookUp USSD").Select stage i cant figure this bit out. as from closing down the program last night and opening it today the error code has now changed to:

Run-time error '424': Object Required
 
Upvote 0
p45cal was asking if you have named ranges called Site and Location - eg if on xl2007+ you would view thes in Name Manager from the ribbon
 
Upvote 0
Oops sorry miss read it.

Yes in my excel sheet i have named ranges called site, location and type,

I have now restart the sheet and got the same 1004 error message and it seem to be point to another part of the code:

Sub Add()
Add_Printer.Show vbModal
End Sub

this was working fine yesterday.
 
Upvote 0
What's the error? Incidentally, I would rename that sub to something else - there are too many other methods named Add. Having this too will just cause confusion and may cause conflicts too.
 
Upvote 0
First, let's check that these 3 named ranges exist and are of the right size/shape:
Press F5 (=GoTo dialogue box) and type in the reference field:
Site
click OK.
The range should be selected. Is it? Is it the expected size? Is it the expected range?

Do the same with the other two; Location and Printer_Type. Get the spelling absolutely right.
 
Upvote 0
I checked all 3 names and they all exists and all spelt correct.

i am getting really confused at what this could be.
 
Upvote 0
I have said the error message in the title and in the first post:

Run-Time error '1004': Method 'Ranger' of Object'_Worksheet' Failed.

but p45cal found it i miss typed the _ so thanks a lot p45cal i could not see it for looking.

but fixing that has cause another message:

Run-time error '28': Out of stack space

can anyone help with this ?
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,172
Members
448,554
Latest member
Gleisner2

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