Load a combobox based on selection from another combobox - vba

kelly mort

Well-known Member
Joined
Apr 10, 2017
Messages
1,826
Office Version
  1. 2016
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
50,903
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
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

Well-known Member
Joined
Apr 10, 2017
Messages
1,826
Office Version
  1. 2016
Platform
  1. Windows
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

Well-known Member
Joined
Apr 10, 2017
Messages
1,826
Office Version
  1. 2016
Platform
  1. Windows
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:

Code:
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
50,903
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

How about
VBA Code:
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

Well-known Member
Joined
Apr 10, 2017
Messages
1,826
Office Version
  1. 2016
Platform
  1. Windows
Super cool! !!!

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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
50,903
Office Version
  1. 365
Platform
  1. Windows
To some extent it depends on the combo settings.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,810
Messages
5,574,437
Members
412,593
Latest member
VBA Novice123
Top