third level of dynamic combo box

[da.se]

Hi, and Happy New Year...

This is my first time in creating a user form, and I encountered a problem with third level of dynamic combo box in user form.
Can someone help me with this problem, preferably with some explanation of the code.

A brief explanation of the problems:

- there are 3 combo boxes that supposedly link to each other. Ideally, the first level will generate list of the second level, and the second level will generate list of the third level.
- I am using "select case" in defining the second level (easiest one in my opinion, or if you could direct me to other similar function).
- I can't figure it out how to connect the third to the second level.

Thank you for your time.

I've included an example file.

[LeoTaxi]

Private Sub cbcat1_Click()
With Sheets("category")
    mycol = .Cells.Find(cbcat1, .Cells(1), lookat:=xlPart).Column
    lr = .Cells(.Rows.Count, mycol).End(xlUp).Row
    arr = .Range(.Cells(2, mycol), .Cells(lr, mycol))
    cbcat2.List = arr
End With
End Sub

Private Sub cbcat2_Click()
With Sheets("category")
    mycol = .Cells.Find(cbcat2, .Cells(1), lookat:=xlPart).Column
    lr = .Cells(.Rows.Count, mycol).End(xlUp).Row
    arr = .Range(.Cells(2, mycol), .Cells(lr, mycol))
    cbcat3.List = arr
End With
End Sub

Kind regards
Leo

[meabrams]

The reason yours is failing on the 3rd cat is you are trying to use .ListIndex instead of .Value

c2_red = Me.cbcat2.Value

[meabrams]

Leo's way is better but I just wanted to see what was causing your way to fail.

.ListIndex grabs the index number from 0 to n where as .Value grabs the Value

[da.se]

Hello Leo. Thank you for the help.

If you don't mind, could you explain step by step of the code?
I'm trying to understand the code, to be able to replicate it.
I have tried to replicate it with more category, but the 2nd level show the same one as the first one.
Is it have to do with the find function?

Thank you again.

[LeoTaxi]

I ame not good in explication but give it a try
1 code for each ComBobox

Code for first ComboBox has to be in Userform_Ini......
Others in ComboBox_Click

Private Sub cbcat1_Click()
With Sheets("category")  ' sheet name with data
    mycol = .Cells.Find(cbcat1, .Cells(1), lookat:=xlPart).Column  ' find column that contains the contents of ComboBox cbcat1
    lr = .Cells(.Rows.Count, mycol).End(xlUp).Row  ' find the last row of this column
    arr = .Range(.Cells(2, mycol), .Cells(lr, mycol))  ' put the values of this column in a array
    cbcat2.List = arr  ' put the array in the list of next ComBobox
End With
End Sub

Kind regards
Leo

[da.se]

Hi Leo, thanks for the explanation.

I tried to replicate it again, but now the code is not working.
I read some code for generating the combo box, but when I applied the code. I run into error code 70. Therefore I delete it.

I've included the example in attachment.
The "Room" combo box derrived from the "Location" combo box.
The "Category", "Type", and "Made From" combo box is the 1st. 2nd, and 3rd dependant combo boxes.

Sorry for the brief explanation.

[LeoTaxi]

In this file it is not clear to me what has to come in the depend boxes

you have 5 locations, lets say we choose 2 what has to come in next ComBobox ?

Kind regards
Leo

[da.se]

Hi Leo.

In the file,
- location combo box refers to "Kode 00" or in the name library "lok"
* "01" refers to name library "ger"
* "02" and "03" refers to name library "pas"
* "04" refers to name library "bin"
* "05" doesn't refers to anything (blank)

- category combo box refers to "kode 04" or name library "bar"
* "ACO" refer to name library "kode05"
* ...
* "KBM" refer to name library "kode14"
- in type combo box ("kode 04"), each of "ETN", "PNJ", "MUS", "PNR", "KMT", "PRT", and "ATK" derivatives, will refer to "kode15" or in the name library "bah" (third level). While "ACO", "CET", and "KBM" doesn't have derivatives (doesn't have a third level)
- the condition and status combo box is just simple one level combo box.

I hope that I explain it right

Thank you.

[LeoTaxi]

Hi Leo.

In the file,
- location combo box refers to "Kode 00" or in the name library "lok"
* "01" refers to name library "ger"
* "02" and "03" refers to name library "pas"
* "04" refers to name library "bin"
* "05" doesn't refers to anything (blank)

- category combo box refers to "kode 04" or name library "bar"
* "ACO" refer to name library "kode05"
* ...
* "KBM" refer to name library "kode14"
- in type combo box ("kode 04"), each of "ETN", "PNJ", "MUS", "PNR", "KMT", "PRT", and "ATK" derivatives, will refer to "kode15" or in the name library "bah" (third level). While "ACO", "CET", and "KBM" doesn't have derivatives (doesn't have a third level)
- the condition and status combo box is just simple one level combo box.

I hope that I explain it right

Thank you.

see red lines


Kind regards
Leo

[da.se]

Hmmm...

So basically,

What I intended is,
There are 2 series of combo boxes which has its own dependent combo boxes. And one of them has a third dependent combo box.

But,
Because of the way the code works, there can be only one series of combo boxes?

[LeoTaxi]

No

i see 2 times 04 with other library

[da.se]

Ah.. I see.

The first "04" refers to the cell A5.
It's part of the "Location" combo box list.
The second, which is "Kode 04", refers to the cell E1.
It's a text data. In the name library, the data in column E are named "bar".

Will it be a problem? naming it almost identical?

[da.se]

Hi Leo.

i see 2 times 04 with other library

I'm sorry, but can you be specific?
I tried to look for duplicate, but didn't find any (or maybe I didn't see it).

[LeoTaxi]

If you want depended boxes
the code has to search (search criteria is the contents of previus combo) the column where is the data
in this file not possible

Cheers
Leo

[da.se]

Thank you Leo.
Good day to you.