Run-time error 1004 - Range of object failed - VBA 2013

Question

I'm getting a run time error 1004, Method 'Range' of object '_Worksheet' failed.

This code worked before on a draft of this project, but when imported into new project file, i get this.... none of the named ranges or worksheet names have changed from previous version. Please help!

Private Sub UserForm_Initialize()
'Set Variables to populate combobox
Dim rngProjects As Range
Dim ws1 As Worksheet

Set ws1 = Worksheets("Validation")

For Each rngProjects In ws1.Range("Projects")

Me.cboProject.AddItem rngProjects.Value
Me.cboAccount.AddItem rngProjects.Value

Next rngProjects

'Add static data for combobox
Me.cboTransactionType.AddItem "Income"
Me.cboTransactionType.AddItem "Expense"

It does not give me a line, does not even give the option to Debug. prntscr.com/bmrx1z — Jon Whiteford
I think Kyle is saying do this: Set ws1 = ActiveWorkbook.Worksheets("Validation") — mechanical_meat
Off Topic : - I made a common newb mistake on the old draft version, I set a login form, and made a typo on sheet to check and now I'm locked out forever. :D — Jon Whiteford
Don't worry typos are a common thing, you may delete the question if so — Sgdva

Ralph Ralph · Accepted Answer · 2016-06-29T21:51:57

It seems to me that either the sheet name changed (sometimes a leading or trailing space is not recognizable) or the name for the named range changed. Furthermore, it could also be that the named range moved to another sheet (other than the validation sheet).

If you check all of the above and furthermore use explicit references as suggested in the comments then you should be able to find the problem. Here is your code with some additional checks. Give it a try and let us know if this works or made you aware of the problem.

Option Explicit

Private Sub UserForm_Initialize()
'Set Variables to populate combobox
Dim rngProjects As Range
Dim ws1 As Worksheet
Dim bolFound As Boolean
Dim nm As Name

For Each ws1 In ThisWorkbook.Worksheets
    If ws1.Name = "Validation" Then bolFound = True
Next ws1
If bolFound = False Then
    MsgBox "Required sheet not found."
    Exit Sub
End If

Set ws1 = Worksheets("Validation")

For Each rngProjects In ws1.Range(ws1.Cells(8, 1), ws1.Cells(ws1.Rows.Count, 1).End(xlUp))
    frmAddTransaction.cboProject.AddItem rngProjects.Value
    frmAddTransaction.cboAccount.AddItem rngProjects.Value
Next rngProjects

'Add static data for combobox
frmAddTransaction.cboTransactionType.AddItem "Income"
frmAddTransaction.cboTransactionType.AddItem "Expense"

End Sub

As commented below, the indirect named range has been removed. Instead, the sheet is now directly referenced in the code. Like this any change of the named range will have no impact on the code. Instead, the offset of 8 rows is written in the VBA where it is slightly more secure.

Run-time error 1004 - Range of object failed - VBA 2013

1 Answers