Load a combobox based on selection from another combobox - vba

[kelly mort]

I have two comboboxes and when I select say x from Combobox1, Then I would want to load into combobox2 all data in column B which have corresponding item matching the selection in combobox1.

The combobox1 will contain x, y, Z as test data and column c will house them.

My data start from row 4 the first three rows are headers

Thanks

 

[Fluff]

How about

Private Sub ComboBox1_Click()
   Dim lst As Variant
   
   With Range("B4", Range("B" & Rows.Count).End(xlUp))
      lst = Filter(Evaluate("transpose(if(" & .Offset(, 1).Address & "=" & Chr(34) & Me.ComboBox1.Value & Chr(34) & "," & .Address & ",""#""))"), "#", False)
   End With
   Me.ComboBox2.List = lst
End Sub

 

[kelly mort]

Very cool!!

But one issue is that instead of having the date show up in combobox2 as dd-mm-yy, it is showing its serial number.

How do I fix that?

 

[kelly mort]

I observed that when I run the code and the sheet is not active, it fails to do the job

Though I have been able to qualify the sheet as:

Private Sub ComboBox1_Click()
   Dim lst As Variant, db  As Worksheet 

  Set db = Sheets("Sheet1")
   
   With db.Range("B4", db.Range("B" & Rows.Count).End(xlUp))
      lst = Filter(Evaluate("transpose(if(" & .Offset(, 1).Address & "=" & Chr(34) & Me.ComboBox1.Value & Chr(34) & "," & .Address & ",""#""))"), "#", False)
   End With
   Me.ComboBox2.List = lst
End Sub

What did I miss ?

 

[Fluff]

How about

Private Sub ComboBox1_Click()
   Dim Lst As Variant
   Dim db As Worksheet
   Dim i As Long

   Set db = Sheets("Sheet1")
   With db.Range("B4", db.Range("B" & Rows.Count).End(xlUp))
      Lst = Filter(db.Evaluate("transpose(if(" & .Offset(, 1).Address & "=" & Chr(34) & Me.ComboBox1.Value & Chr(34) & "," & .Address & ",""#""))"), "#", False)
   End With
   For i = 0 To UBound(Lst)
      Lst(i) = Format(Lst(i), "dd-mm-yy")
   Next i
   Me.ComboBox2.List = Lst
End Sub

 

[kelly mort]

Super cool! !!!

What's the difference between the click event and the change event?

 

[Fluff]

To some extent it depends on the combo settings.