Userform Combo boxes not linking to multiple different sheets

[ISqueal]

Beginner vba user here on Excel 2013. I am creating a user form with two different combo boxes that draw from two different sheets within the same workbook. I have a last row function included for each combo box as the ranges may change from time to time.

Here is my code:

Sub UserForm_Initialize()


'This code is for the OwnerComboBox. Combo box references the Owners worksheet, it auto updates with each new owner added to the list.


Dim ownersht As Worksheet
Dim ownerLastRow As Long


Set ownersht = ThisWorkbook.Worksheets("Owners")

ownerLastRow = ownersht.Cells(ownersht.Rows.Count, "A").End(xlUp).Row

Me.OwnerComboBox.List = ownersht.Range(Cells(2, 1), Cells(ownerLastRow, 1)).Value


'This code is for the GCComboBox. Combo box references the General Contractors worksheet, it auto updates with each new owner added to the list.


Dim gcsht As Worksheet
Dim gcLastRow As Long


Set gcsht = ThisWorkbook.Worksheets("General Contractors")

gcLastRow = gcsht.Cells(gcsht.Rows.Count, "A").End(xlUp).Row

Me.GCComboBox.List = gcsht.Range(Cells(2, 1), Cells(gcLastRow, 1)).Value


End Sub


I keep getting an error message saying "Method 'Range' of object '_Worksheet' failed".

Can anyone tell me where my code is wrong?

Thanks.

 

[Norie]

Try this.

Private Sub UserForm_Initialize()
Dim gcsht As Worksheet
Dim gcLastRow As Long
Dim ownersht As Worksheet
Dim ownerLastRow As Long

    Set ownersht = ThisWorkbook.Worksheets("Owners")
    With ownersht
        ownerLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        Me.OwnerComboBox.List = .Range(.Cells(2, 1), .Cells(ownerLastRow, 1)).Value
    End With

    Set gcsht = ThisWorkbook.Worksheets("General Contractors")
    With gcsht
        gcLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        Me.GCComboBox.List = .Range(.Cells(2, 1), .Cells(gcLastRow, 1)).Value
    End With

End Sub

 

[ISqueal]

Works perfectly, thank you!

I just figured out another fix for it which was to activate each worksheet right before their respective procedures but your fix does that seamlessly while keeping the user on whatever sheet they have selected.

Thanks so much!