Items in combobox added with condition of another combobox

[tiger837]

Hi everyone,

I have a problem for a userform which uses comboboxes.
So basically I have column A with "area" items and column B with "process" items. I don't know how many different items I have in theses columns. But everytime you have an area, you have a process written next to it.
I want a userform with a first combobox that will allow us to choose a area written in column A. Even if the area is named multipled times, it will be displayed only once on the combobox. Furthermore, all areas in the combobox are sorted A->z.
I have written this code that is working, but I can't sort the areas a->Z.

Private Sub Userform_Initialize()
    Dim j As Integer
    
    For j = 4 To Range("A65536").End(xlUp).Row
        AreaBox = Range("A" & j)
        If AreaBox.ListIndex = -1 Then AreaBox.AddItem Range("A" & j)
    Next j
    
End Sub

After that, I want a 2nd combobox that will list the process items that exist for the area choosen. Like for the first combobox, each process items will appeared only once, and sorted a->z.
It's for this combobox that I have more trouble because with the following combobox, all processes appeared even if they don't exist for the selected area.

Private Sub AreaBox_change()


    Dim r As Range
    Dim c As Range
    
    Set r = Range("A4:A200")


    For Each c In r
        If c.Value = AreaBox.Value Then
            ProcessBox.AddItem c.Offset(0, 1).Value
            If ProcessBox.ListIndex = -1 Then ProcessBox.AddItem c.Offset(0, 1)
        End If
    Next c


End Sub

If anyone have a solution for theses problems, I'll be glad to ear it ! Thanks

 

[Fluff]

Hi & welcome to MrExcel.
How about something like

[COLOR=#0000ff]Option Explicit
Dim Dic As Object
[/COLOR]
Private Sub ComboBox1_Click()
   Dim Ky As Variant
   Dim Lst As Object
   
   Set Lst = CreateObject("system.collections.arraylist")
   Me.ComboBox2.Clear
   For Each Ky In Dic(Me.ComboBox1.Value).Keys
      Lst.Add Ky
   Next Ky
   Lst.Sort
   Me.ComboBox2.List = Lst.toarray
   
End Sub

Private Sub UserForm_initialize()
   Dim Cl As Range
   Dim Ws As Worksheet
   Dim Lst As Object
   Dim Ky As Variant
   
   Set Ws = Sheets("pcode")
   Set Dic = CreateObject("Scripting.dictionary")
   Set Lst = CreateObject("system.collections.arraylist")
   For Each Cl In Ws.Range("A2", Ws.Range("A" & Rows.Count).End(xlUp))
      If Not Dic.Exists(Cl.Value) Then
         Dic.Add Cl.Value, CreateObject("scripting.dictionary")
         Dic(Cl.Value).Add (Cl.Offset(, 2).Value), Nothing
      Else
         Dic(Cl.Value)(Cl.Offset(, 2).Value) = Empty
      End If
   Next Cl
   For Each Ky In Dic.Keys
      Lst.Add Ky
   Next Ky
   Lst.Sort
   Me.ComboBox1.List = Lst.toarray
End Sub

The two lines in blue must go at the very top of the module, before any code.

 

[tiger837]

Hi,

Thanks for the quick answer. I've been trying to test it for the past 15min, but now I can't launch the userform because as I click on the command button, it goes on "error '9': subscript out of range", while before it was working perfectly...
The macro related to the commandButton which launch the userform is simply:

Private Sub Button10_Click()


UseF.Show


End Sub

 

[Fluff]

Did you change this line

 Set Ws = Sheets("pcode")

To reflect the name of the sheet?

 

[tiger837]

Indeed.... my bad, I missed that one sorry..
Otherwise your code is working perfectly, thanks !
Just quick questions for me to understand your code better:
- Why create a collection and not a list ?
- What the Dic (dictionary) in the end ?
- and what's the key used for ?

 

[tiger837]

(sorry couldn't find the edit button)

Because, let's say I want to do the same thing with a 3rd combobox (related to item in column C) whick will display item that exists for the selected process on the 2nd combobox.
I would have to create roughly the same private sub ComboBox3_Click() but also create another dictionary ?

 

[Fluff]

An ArrayList is quick & simple to sort, which is why I used it.
The Key is part of the Dictionary https://excelmacromastery.com/vba-dictionary/
I'm not sure what you mean by

What the Dic (dictionary) in the end ?

 

[tiger837]

Sorry, it was "What the dictionary is used for ?

Besides, I did have a question for a 3rd column:

(sorry couldn't find the edit button)

Because, let's say I want to do the same thing with a 3rd combobox (related to item in column C) whick will display item that exists for the selected process on the 2nd combobox.
I would have to create roughly the same private sub ComboBox3_Click() but also create another dictionary or create and extract 2 keys?

 

[Fluff]

To run it on 3 dependant Combos, use

Option Explicit
Dim Dic As Object

Private Sub ComboBox1_Click()
   Dim Ky As Variant
   Dim Lst As Object
   
   Set Lst = CreateObject("system.collections.arraylist")
   Me.ComboBox2.Clear
   Me.ComboBox3.Clear
   For Each Ky In Dic(Me.ComboBox1.Value).Keys
      Lst.Add Ky
   Next Ky
   Lst.Sort
   Me.ComboBox2.List = Lst.toarray
End Sub
Private Sub ComboBox2_Click()
   Dim Ky As Variant
   Dim Lst As Object
   
   Set Lst = CreateObject("system.collections.arraylist")
   Me.ComboBox3.Clear
   For Each Ky In Dic(Me.ComboBox1.Value)(Me.ComboBox2.Value).Keys
      Lst.Add Ky
   Next Ky
   Lst.Sort
   Me.ComboBox3.List = Lst.toarray
End Sub

Private Sub UserForm_initialize()
   Dim Cl As Range
   Dim Ws As Worksheet
   Dim Lst As Object
   Dim Ky As Variant
   
   Set Ws = Sheets("pcode")
   Set Dic = CreateObject("Scripting.dictionary")
   Set Lst = CreateObject("system.collections.arraylist")
   For Each Cl In Ws.Range("A2", Ws.Range("A" & Rows.Count).End(xlUp))
      If Not Dic.Exists(Cl.Value) Then
         Set Dic(Cl.Value) = CreateObject("scripting.dictionary")
      End If
      If Not Dic(Cl.Value).Exists(Cl.Offset(, 1).Value) Then
         Set Dic(Cl.Value)(Cl.Offset(, 1).Value) = CreateObject("scripting.dictionary")
      End If
      Dic(Cl.Value)(Cl.Offset(, 1).Value)(Cl.Offset(, 2).Value) = Empty
   Next Cl
   For Each Ky In Dic.Keys
      Lst.Add Ky
   Next Ky
   Lst.Sort
   Me.ComboBox1.List = Lst.toarray
End Sub

 

[tiger837]

Thank you very much Fluff, it works perfectly ! :D