VBA Error Message Run-Time Error 1004

[nuttycow13]

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.

 

[p45cal]

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?

 

[nuttycow13]

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

 

[Firefly2012]

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

 

[nuttycow13]

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.

 

[Firefly2012]

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.

 

[p45cal]

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.

 

[nuttycow13]

I checked all 3 names and they all exists and all spelt correct.

i am getting really confused at what this could be.

 

[Firefly2012]

What's the error?

You haven't stated what the new error is - number and description please

 

[nuttycow13]

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 ?