UserForm_Initialize Event Multiple Worksheets

03856me

Active Member
Joined
Apr 4, 2008
Messages
297
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

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

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
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.
 
Upvote 0
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
'-------------------------------------------------------------------
 
Upvote 0
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.

Code:
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.

Code:
For Each oneCell in Workbooks("someWorkbook.xlsm").Name("NamedRange").RefersToRange
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,915
Members
448,532
Latest member
9Kimo3

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