Dependent List ComboBox Question

[wadeamaral]

There are two current sheets in my workbook, "Entered" and "Details" the following code is in a userform that is currently working when activated from "Details" sheet however I receive a Run-time error '1004' Method 'Range' of object '_Worksheet' failed error when accessing the userform from "Entered" sheet. It highlights the

 Set Rng = Ws.Range(Cells(2, col), Cells(LastRow, col)) ' Chooses correct range

portion of my code. I thought since I specified the "Details" sheet as Ws it shouldn't be giving me this problem. I'm stumped, thanks in advance for the help.

Private Sub cboEquip_Change()

Dim SourceData As Variant
Dim col As Variant
Dim LastRow As Long
Dim Rng As Range
Dim Ws As Worksheet



Set Ws = Worksheets("Details") ' Change name as required


col = WorksheetFunction.Match(Me.cboEquip.Value, Ws.Range("1:1"), 0) 'Matches text from cboEquip to appropriate column in Details sheet
LastRow = Ws.Cells(Ws.Rows.Count, col).End(xlUp).Row ' Sets number of items in list

Set Rng = Ws.Range(Cells(2, col), Cells(LastRow, col)) ' Chooses correct range






' Changes cboUnit based on changes from cboEquip

SourceData = Rng.Value
With Me.cboUnit
.Clear
.List = SourceData
.ListIndex = 0
End With
End Sub

 

[wadeamaral]

I figured it out. I have to fully qualify the sheet I am referring to. My mistake. The code for those that are wondering:

Set Rng = Sheets("Details").Range(Sheets("Details").Cells(2, col), Sheets("Details").Cells(LastRow, col)) ' Chooses correct range