Dynamically adding column values based on combo box selection

Question

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:

try debug.? UserForm1.floor.Value before, make sure you definitely have what you are expecting. Also, what does go into the combo if anything? — Nathan_Sav
What is the problem? Is it the wrong values or not being populated at all or? — Kubie
you should clear your office combo box before adding items. First action in floor_Change should be UserForm1.office.Clear otherwise it will add more and more items to the combo box each time you change the floor. — Pᴇʜ

Jason Kelly Jason Kelly · Accepted Answer · 2018-11-13T19:08:27

'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

Dynamically adding column values based on combo box selection

1 Answers