Odtnhj

I need your help. It seems what I have written in code does not accomplish what I am trying to do here.

The objective would be to have 2 userform combo boxes one for the (floor) values which are manually added once [3,4,5] and the other combo boxes (offices) in which values are dynamically added based on the selection made in the floor selection box.

Let's say for example that if I chose the value [3] in my floor combo box that the office combo box would contain the following values:

A-01
A-02
A-03
A-04
A-05
A-06
A-07
A-08

I thought this code would work but it doesn't:

'Cells(row, col)

Private Sub floor_Change()
 lRow = Sheets("Office Spaces").UsedRange.Rows.Count

 With Sheets("Office Spaces")
 For i = 2 To lRow
 If .Cells(i, 1).Value = UserForm1.floor.Value Then
 UserForm1.office.AddItem .Cells(i, 2).Value
 End If
 Next i
 End With
End Sub

Here's what the data looks in my excel sheet:

'Cells(row, col)

Private Sub floor56_Change()

UserForm1.office.Clear

Dim sh
Dim rw

Set sh = Sheets("Office Spaces")

For Each rw In sh.Rows

 If sh.Cells(rw.row, 1).Text = UserForm1.floor.Value Then

 UserForm1.office.AddItem (sh.Cells(rw.row, 2).Value)

 End If

Next rw

End Sub

or

Private Sub floor_Change()

 If UserForm1.floor.Value <> "" Then

 UserForm1.office.Clear

 Dim ws
 Set ws = ThisWorkbook.Worksheets("Office Spaces")

 Dim rng
 Set rng = ws.Range("A:A")

 For Each cell In rng

 If cell.Text = UserForm1.floor.Value Then

 UserForm1.office.AddItem (cell.Offset(0, 1).Value)

 End If

 Next cell

 End If

End Sub