Dependent ComboBox that acts as something like Index Match

ivanian · Apr 8th 2016

Hello Guys, I'm fairly new to VBA excel and got stuck with my dependent ComboBox. Appreciate it if someone could lend a hand or guide me to a right direction.

The Setup: 2 ComboBox (1 parent and 1 child). 2 Column table (I assigned a namerange for both columns) — Column1 is consist of recurring types. Say TypeA, TypeB and so on and Column2 is consist of unique values that is related to Column1.

ComboBox1 is populated by the items from Column1 (I added function to not include duplicates). So it's basically just TypeA, TypeB and TypeC.

[TABLE="class: grid, width: 200"]

[tr]

[td]

Column1

[/td]

[td]

Column2

[/td]

[/tr]

[tr]

[td]

TypeA

[/td]

[td]

Val1

[/td]

[/tr]

[tr]

[td]

TypeB

[/td]

[td]

Val2

[/td]

[/tr]

[tr]

[td]

TypeC

[/td]

[td]

Val3

[/td]

[/tr]

[tr]

[td]

TypeA

[/td]

[td]

Val4

[/td]

[/tr]

[tr]

[td]

TypeA

[/td]

[td]

Val5

[/td]

[/tr]

[tr]

[td]

TypeB

[/td]

[td]

Val6

[/td]

[/tr]

[tr]

[td]

TypeA

[/td]

[td]

Val7

[/td]

[/tr]

[tr]

[td]

TypeA

[/td]

[td]

Val8

[/td]

[/tr]

[/TABLE]

So what I want to happen is for the ComboBox2 to return a list depending on the value of ComboBox1.

So for example, if the user picked TypeA, ComboBox2 values or list will be Val1, Val4, Val5, Val7, Val8.

Any idea how should I implement it? I've been trying to search online but I couldn't find the exact function that I needed.
I could just put a namerange for each groups and just return it with a case but overtime, the source gets updated so I need it to be flexible.

Hopefully what I want to happen is possible. Appreciate any help from the experts out there.

Many thanks.

skywriter · Apr 8th 2016

Re: Dependent ComboBox that acts as something like Index Match

It's always good if you include a sample workbook, that way there is no question as to whether your combo box is on a sheet or a user form or it's just a data validation drop down box, and we do not have to try to recreate your workbook for testing purposes.

When you click reply there will be a new button labeled go advanced, click on that button then there will be a paperclip icon in the toolbar, click on that icon and follow the instructions.

ivanian · Apr 8th 2016

Re: Dependent ComboBox that acts as something like Index Match

Hello, thanks for your response and my apologies. I have attached the sample file. So to make it more clear.

ComboBox1 is consist Level Column (I named it itemLvl).

When user picked a level range from ComboBox1, ComboBox2 will be filled with items from Name Column (I named it itemName) that matches the level range that was picked.

Hopefully I have explained it well.

Thanks.

skywriter · Apr 8th 2016

Re: Dependent ComboBox that acts as something like Index Match

Try this user form code.

Code

Private Sub cbx1_AfterUpdate()
Me.cbx2.Clear
Dim arrData, c As Long
arrData = Worksheets("Sample").UsedRange.Columns("A:B").Value
    For c = LBound(arrData) To UBound(arrData)
        If arrData(c, 2) = Me.cbx1.Value Then cbx2.AddItem arrData(c, 1)
    Next c
End Sub

ivanian · Apr 8th 2016

Re: Dependent ComboBox that acts as something like Index Match

Sweet! Exactly what I needed. Thank you so much,.

Just one favor please. Do you mind walking me through the code? I'm really new to VBA excel and it would be nice if I could get a better grasp of how the code above works.
From what I understood is:

You declared Column A and B as a 2 dimension array. If I'm correct, is it possible set a whole table as a multi-dimensional array?

Code

arrData = Worksheets("Sample").UsedRange.Columns("A:B").Value

Loop through the array where LBound is the starting point of Array? and UBound is ending point?

Code

For c = LBound(arrData) To UBound(arrData)

Find the matching value from cbx1 from array where arrData(c,2) points to the 2nd dimension of array which is ColumnB and arrData(c, 1) is the 1st dimension is ColumnA.

Code

If arrData(c, 2) = Me.cbx1.Value Then cbx2.AddItem arrData(c, 1)

Did I get it right?

skywriter · Apr 8th 2016

Re: Dependent ComboBox that acts as something like Index Match

You did well.
Check out the link below the code, it's a great resource.

Code

Sub Test()
Dim myArray
myArray = Worksheets("Sample").ListObjects("tbl_item").DataBodyRange
End Sub

VBA Coding For Tables.

ivanian · Apr 9th 2016

Re: Dependent ComboBox that acts as something like Index Match

Nice. I will definitely look into that. Again, thank you and one more thing.

I just realized that I'm gonna be using that procedure a lot and decided to separate it and make a function that I could call for that. The below code works but I'm not sure if there's another way to do it, like a cleaner way because if I coded it incorrect it might cause a problem in the future.

The Function:

Code

Public Function lookUp(tbl As Variant, cbx1 As ComboBox, cbx2 As ComboBox)
    cbx2.Clear
    Dim arrData, c As Long
    arrData = tbl
    For c = LBound(arrData) To UBound(arrData)
        If arrData(c, 2) = cbx1.Value Then cbx2.AddItem arrData(c, 1)
    Next c
End Function

How I call it:

Code

Private Sub cbx1Change()
    Dim list
    list = Worksheets("DB").ListObjects("tbl").DataBodyRange
    
    lookUp list, cbx1, cbx2
End Sub

I hope you don't mind me asking one question to another.

Thanks

skywriter · Apr 9th 2016

Re: Dependent ComboBox that acts as something like Index Match

If you've tested it and it works, then I don't see an issue.

I have no experience with passing combo boxes to another procedure so I don't know if there will be any issues.

I can tell you that in the event someone types an entry into the combo box instead of picking a value from the list, the change event fires every time a character is typed, so if they typed t-e-s-t, then the event fires 4 times.

You may want to test that to make sure you don't have an issue.
That is why I chose the after update event.

One last caveat would be don't name functions the same as existing Excel function.

I would call that myLookup or something that isn't already the name of an Excel function.

ivanian · Apr 10th 2016

Re: Dependent ComboBox that acts as something like Index Match

I see. I will certainly look into that and make sure to make a habit out of it.

Thanks for all the help.

skywriter · Apr 10th 2016

Re: Dependent ComboBox that acts as something like Index Match

My pleasure, thanks for the feedback. :cool:

ivanian · Apr 10th 2016

Re: Dependent ComboBox that acts as something like Index Match

I'm not sure if this is not against the forum rules but if you have time and if you don't mind — could you please take a look into this thread please ? I'm really stuck.

http://www.ozgrid.com/forum/showthread.php?t=199389

Thanks

Dependent ComboBox that acts as something like Index Match

Participate now!

Share