Items in combobox added with condition of another combobox

tiger837

New Member
Joined
Dec 3, 2018
Messages
8
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.

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

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
60,138
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
How about something like
Code:
[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

New Member
Joined
Dec 3, 2018
Messages
8
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:
Code:
Private Sub Button10_Click()


UseF.Show


End Sub
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
60,138
Office Version
  1. 365
Platform
  1. Windows
Did you change this line
Code:
 Set Ws = Sheets("pcode")
To reflect the name of the sheet?
 

tiger837

New Member
Joined
Dec 3, 2018
Messages
8

ADVERTISEMENT

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

New Member
Joined
Dec 3, 2018
Messages
8
(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 ?
 

tiger837

New Member
Joined
Dec 3, 2018
Messages
8
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
60,138
Office Version
  1. 365
Platform
  1. Windows
To run it on 3 dependant Combos, use
Code:
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
 

Forum statistics

Threads
1,136,803
Messages
5,677,818
Members
419,722
Latest member
Rizzol

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top