Dynamically populate a combobox based on selection from another combobox

[kelly mort]

    ComboBox1.List = Application.WorksheetFunction.Transpose(ThisWorkbook.Names("MyRange").RefersToRange)

Hi and hello to you all. It's been a while since I visited this site.

I am doing well and hope you are too.

I have a challenge here with me.

The above line is what I am using to fill my combobox currently.

The named range "MyRange" is from B4 to last used cell in same column. I am using a dynamic named range for that and it's working great.

The goal now is to only load part of the data from column B into the combobox.

Combobox2 will determine what I will load into combobox1.


Column C contains item categories like x y z. They are sorted so items are grouped.

And those items are loaded into combobox2.

So when I select x in combobox2, then my combobox1 should load only items from column B with x category.


Thanks

 

[mikerickson]

It sounds like currenly you have X,Y,Z in ComboBox2 and you want ComboBox1 to be filled with the items (from Range("myRange")) that match the selection from ComboBox2.

If so

Dim oneCell As Range

ComboBox1.Clear

For each oneCell in ThisWorkbook.Names("MyRange").RefersToRange.Columns(1).Cells
    If OneCell.Offset(0, 1).Value = ComboBox2.Value Then
        ComboBox1.AddItem OneCell.Value
    End If
Next oneCell

 

[Fluff]

How about

Dim Ufdic As Object
Private Sub ComboBox2_Click()
   Me.ComboBox1.Clear
   Me.ComboBox1.List = Ufdic(Me.ComboBox2.Value).Keys
End Sub

Private Sub UserForm_Initialize()
   Dim Ary As Variant
   Dim i As Long

   Set Ufdic = CreateObject("Scripting.dictionary")
   With ActiveSheet
      Ary = .Range("B4:C" & .Range("B" & Rows.Count).End(xlUp).Row).Value2
   End With
   For i = 1 To UBound(Ary)
      If Not Ufdic.Exists(Ary(i, 2)) Then Ufdic.Add Ary(i, 2), CreateObject("scripting.dictionary")
      Ufdic(Ary(i, 2))(Ary(i, 1)) = ""
   Next i
   Me.ComboBox2.List = Ufdic.Keys
End Sub

The first line must go at the very top of the module, before any code.

 

[kelly mort]

It sounds like currenly you have X,Y,Z in ComboBox2 and you want ComboBox1 to be filled with the items (from Range("myRange")) that match the selection from ComboBox2.

If so

Dim oneCell As Range

ComboBox1.Clear

For each oneCell in ThisWorkbook.Names("MyRange").RefersToRange.Columns(1).Cells
    If OneCell.Offset(0, 1).Value = ComboBox2.Value Then
        ComboBox1.AddItem OneCell.Value
    End If
Next oneCell

Mike,

You are great.

Thanks and have a wonderful time. Stay safe