UserForm_Initialize Event Multiple Worksheets

[03856me]

Excel 2007 Combo Box data find problem:

I have built a userform with two combo boxes. Both are pointing to tables, but the tables are on different worksheets. On my initialization event how do I set the worksheet for the second table? Is there a way to write the code and not have to specify the worksheet if you are using Arrays? This seems so simple but after two hours searching I need help!

foresterTable is on the worksheet called "lookups"
contractorsTable is on the worksheet called "tblContractors"

Here is the code I have so far but it is giving me this error "Run-time error '1004' Method 'Range' of object '_Worksheet' failed:

'---------------------------------------------------------------------
Private Sub UserForm_Initialize()
Dim myForester As Range
Dim ws As Worksheet
Set ws = Worksheets("lookups")

For Each myForester In ws.Range("foresterTable[Forester]")
With Me.cbo_forester
.AddItem myForester.Value
End With
Next myForester

Dim myContractor As Range
For Each myContractor In ws.Range("contractorTable[Contractors]")
With Me.cbo_contractor
.AddItem myContractor.Value
End With
Next myContractor

End Sub

'---------------------------------------------------------------------

 

[Norie]

You are using the same worksheet for both tables.

All you should need to do is add the right worksheet for the right table or you might be able to just remove the worksheet reference part.

 

[03856me]

Just figured it out and you are right, here is my corrected code and it works perfectly, just in case this will help others

'-------------------------------------------------------------------
Private Sub UserForm_Initialize()
Dim myForester As Range
Dim ws As Worksheet
Set ws = Worksheets("lookups")

For Each myForester In ws.Range("foresterTable[Forester]")
With Me.cbo_forester
.AddItem myForester.Value
End With
Next myForester

Set ws = Worksheets("tblContractors")
Dim myContractor As Range
For Each myContractor In ws.Range("contractorTable[Contractors]")
With Me.cbo_contractor
.AddItem myContractor.Value
End With
Next myContractor
End Sub
'-------------------------------------------------------------------

 

[mikerickson]

If the scope of the names is at the Worksheet level, there is no need to specify the sheet. The sheet is "part" of the named range.

Private Sub UserForm_Initialize()
Dim oneCell As Range

For Each oneCell In Range("foresterTable[Forester]")
    With Me.cbo_forester
        .AddItem oneCell.Value
    End With
Next oneCell

For Each oneCell In Range("contractorTable[Contractors]")
    With Me.cbo_contractor
        .AddItem oneCell.Value
    End With
Next oneCell

End Sub

If the userform is housed in a different workbook than the named ranges, this syntax should be used.

For Each oneCell in Workbooks("someWorkbook.xlsm").Name("NamedRange").RefersToRange