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.

 

[Firefly2012]

In your Visual Basic Editor, can you go to the Tools menu and select Options. On the General tab of the dialog that will open, in the Error Trapping section can you make sure that "Break in class module" is selected.

Now, where are you getting this new error?

 

[nuttycow13]

this new error is in another section of the code that use to work:


Private Sub Next_Number_Click()

Sheet1.Activate

If Site = "UKCH" Then
Sheet2.Activate
ElseIf Site = "NLEI" Then
Sheet13.Activate
ElseIf Site = "USHW" Then
Sheet10.Activate
ElseIf Site = "SGSG" Then
Sheet11.Activate
End If

Range("B1").End(xlDown).Select
ActiveCell.Offset(1, 0).Select
ActiveCell.Offset(0, -1).Select
Next_Number = ActiveCell

End Sub

the bold section is the part causing the error code, i am also having problems getting the combobox input to change the active sheet so the next combobox will pick up different lookup depending on the input in the first box

 

[Firefly2012]

is this code in a userform or is the button/control it is attached to on a worksheet?

 

[nuttycow13]

Sorry i should have said that, as u can tell i am new to all this.

its a button on a userform all this is on one userform.

and on the worksheet there is an add button that picks up this userform and then outputs it to a sheet depending on the input.

 

[Firefly2012]

You don't need to Select things to work with them so we can clean your code up a bit.

That being said, what you have shouldn't error (AFAIK). Especially not with a stack overflow. Can you try shutting Excel down (not just closing your workbook) and then re-opening this file and see if you get the same error?

 

[nuttycow13]

ok i have tried to close it down and start again but nothing. still same error. i can show you the whole code if you want, might explain more:

Private Sub CmdAdd_Click()
Dim NextFreeRow As Long

Sheet1.Activate


Cells(NextFreeRow, 2).Value = Printer_Name.Value
Cells(NextFreeRow, 18).Value = Vaild.Value
Cells(NextFreeRow, 14).Value = DNS.Value
Cells(NextFreeRow, 12).Value = IP.Value
Cells(NextFreeRow, 7).Value = Patch.Value
Cells(NextFreeRow, 10).Value = Fax.Value
Cells(NextFreeRow, 6).Value = Serial.Value
Cells(NextFreeRow, 5).Value = Model.Value
Cells(NextFreeRow, 4).Value = Make.Value
Cells(NextFreeRow, 8).Value = Wing.Value
Cells(NextFreeRow, 11).Value = Mac.Value
Cells(NextFreeRow, 12).Value = Host.Value
Cells(NextFreeRow, 17).Value = GIS.Value
Cells(NextFreeRow, 19).Value = Comments.Value
Cells(NextFreeRow, 3).Value = Printer_Type.Value
Cells(NextFreeRow, 9).Value = Location.Value

End Sub

Private Sub Cancel_Click()
Sheet3.Activate
Unload Me
End Sub

Private Sub ClearForm_Click()
Unload Me
Add_Printer.Show vbModal
End Sub

Private Sub Find_Name_Click()
If Site = "SDHQ" Then
Site.Value = "USSD"
End If

Name when clicked
Printer_Name = Sitebox + "-" & Locationbox & "-" & Printer_Type_box & Numberbox
End Sub

Private Sub Next_Number_Click()

Sheet1.Activate

If Site = "UKCH" Then
Sheet2.Activate
ElseIf Site = "NLEI" Then
Sheet13.Activate
ElseIf Site = "USHW" Then
Sheet10.Activate
ElseIf Site = "SGSG" Then
Sheet11.Activate
End If

Range("B1").End(xlDown).Select
ActiveCell.Offset(1, 0).Select
ActiveCell.Offset(0, -1).Select
Next_Number = ActiveCell

End Sub

Private Sub Number_Change()
Numberbox.Text = Format(Number, "0000")
End Sub

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("Type")
With Me.Printer_Type_box
.AddItem Printer_Type.Value
End With
Next Printer_Type

End Sub

 

[p45cal]

The following sub is suspect:

Private Sub CmdAdd_Click()
Dim NextFreeRow As Long

Sheet1.Activate


Cells(NextFreeRow, 2).Value = Printer_Name.Value
Cells(NextFreeRow, 18).Value = Vaild.Value
Cells(NextFreeRow, 14).Value = DNS.Value
Cells(NextFreeRow, 12).Value = IP.Value
Cells(NextFreeRow, 7).Value = Patch.Value
Cells(NextFreeRow, 10).Value = Fax.Value
Cells(NextFreeRow, 6).Value = Serial.Value
Cells(NextFreeRow, 5).Value = Model.Value
Cells(NextFreeRow, 4).Value = Make.Value
Cells(NextFreeRow, 8).Value = Wing.Value
Cells(NextFreeRow, 11).Value = Mac.Value
Cells(NextFreeRow, 12).Value = Host.Value
Cells(NextFreeRow, 17).Value = GIS.Value
Cells(NextFreeRow, 19).Value = Comments.Value
Cells(NextFreeRow, 3).Value = Printer_Type.Value
Cells(NextFreeRow, 9).Value = Location.Value

End Sub

where you Dim NextFreeRow but don't set it to any value before you use it. It will start with a value of zero, so the likes of:
Cells(NextFreeRow, 17).Value = GIS.Value
translate to:
Cells(0, 17).Value = GIS.Value
and Cells(0,17)
doesn't exist.
You need to set NextFreeRow before using it.

 

[nuttycow13]

ok little confused so i can take out NextFreeRow from the line below and just have 0 instead ? is that what you are saying ?

Cells(NextFreeRow, 2).Value = Printer_Name.Value

 

[nuttycow13]

Sorry i missed a little out of the code as well when i posted it before:

Private Sub CmdAdd_Click()
Dim NextFreeRow As Long

Sheet1.Activate
NextFreeRow = ActiveCell

ActiveCell.Offset(0, 1).Select

Cells(NextFreeRow, 2).Value = Printer_Name.Value
Cells(NextFreeRow, 18).Value = Vaild.Value
Cells(NextFreeRow, 14).Value = DNS.Value
Cells(NextFreeRow, 12).Value = IP.Value
Cells(NextFreeRow, 7).Value = Patch.Value
Cells(NextFreeRow, 10).Value = Fax.Value
Cells(NextFreeRow, 6).Value = Serial.Value
Cells(NextFreeRow, 5).Value = Model.Value
Cells(NextFreeRow, 4).Value = Make.Value
Cells(NextFreeRow, 8).Value = Wing.Value
Cells(NextFreeRow, 11).Value = Mac.Value
Cells(NextFreeRow, 12).Value = Host.Value
Cells(NextFreeRow, 17).Value = GIS.Value
Cells(NextFreeRow, 19).Value = Comments.Value
Cells(NextFreeRow, 3).Value = Printer_Type.Value
Cells(NextFreeRow, 9).Value = Location.Value

End Sub

 

[Firefly2012]

I suspect you want:

NextFreeRow = ActiveCell.Row

So NexFreeRow is set to the row of the activecell rather than whatever the activecell contains