VBA UserForm Help - ComboBox to show filtered drop down list

[CAmelb]

Hi Members,

I am a beginner at VBA and would like to ask for some help from fellow members please.

I have a Userform with a ComboBox which accesses a RowSource Range (For example, Column A) in a worksheet.

I would like to filter the items in the ComboBox list (Column A) based on the corresponding data in (For example, Column B)

Example:

(Column A) (Column B)

(A1) Apples (B1) Fruit
(A2) Pears (B2) Fruit
(A3) Bricks (B3) Building
(A4) Bananas (B4) Fruit
(A5) Timber (B5) Building


When I click on the ComboBox I would like it to display only the items in (Column A) based on its corresponding value in (Column B)

Example:

(Drop Down List)
(A3) Bricks
(A5) Timber

I hope my explanation is not confusing.

Thanks in advance.

CA

 

[Ingolf]

Hello,

How to know userform1 that you choose 'building' or 'fruit'. You have to pick them somewhere, maybe from another combobox or textbox.

 

[Fluff]

Try this

Option Explicit
Private Dic As Object

Private Sub ComboBox1_click()
   Me.ComboBox2.Value = ""
   Me.ComboBox2.List = Dic(Me.ComboBox1.Value).keys
End Sub

Private Sub UserForm_Initialize()

   Dim v1 As String, v2 As String
   Dim Cl As Range
   
   Set Dic = CreateObject("scripting.dictionary")
   Dic.CompareMode = vbTextCompare

   With Sheets("pcode")
      For Each Cl In .Range("B2", .Range("B" & Rows.Count).End(xlUp))
         v1 = Cl.Value: v2 = Cl.Offset(, -1).Value
         If Not Dic.Exists(v1) Then
            Dic.Add v1, CreateObject("scripting.dictionary")
            Dic(v1).Add v2, Nothing
         ElseIf Not Dic(v1).Exists(v2) Then
            Dic(v1).Add v2, Nothing
         End If
      Next Cl
   End With
   Me.ComboBox1.List = Dic.keys
End Sub

Combobox1 will have the values from col B & Combobox2 will have the col A values dependant on what you selected in combobox1