Userform Dependant Combobox

**bojum01** · 06-24-2011, 07:07 AM

http://www.teachexcel.com/excel-help/ev.php?i=121128

Further to the question and answers in the thread, link above, regarding Userform Dependant Combobox.
Excellent answer, it does exactly as requested. I am new to forums and am trying to expand my knowledge.

I am trying to do the same sort of thing but with dynamic ranges, variable number of options in each combo box and a group of 4 combo boxes. I.e. one result cascades to the next combo box making sure that an inappropriate result cannot be entered on the userform.

Before trying to work out the code I am working on the excel dynamic ranges using ‘Match’ from the first option to provide the offset reference point for the second ‘Match’.

'=OFFSET((CELL("address",INDIRECT(CONCATENATE("R",ROW($KJ$31)+((MATCH($JQ$25,$KI$32:$KI$131,0))),"c" ,COLUMN($KJ$31)),FALSE))),MATCH($KJ$26,$KJ$32:$KJ$131,0),4,COUNTIF($KJ$32:$KJ$131,$KJ$26),1)

This may be a too complex solution. Perhaps nested 'Match' does not work in excel.
If this does not work I can go back to creating each option list separately, which may be simpler.

I can get four option boxes working sequentially in the excel workbook. However, I am trying to move all user interfaces to userforms.

I know there are effectively two questions here but I am only looking for one solution, which is how to make multiple Userform Dependant Comboboxs of variable length.

All help welcome.

**jaslake** · 06-24-2011, 06:32 PM

Hi bojum01

Attached is a sample of Dependent ComboBoxes on a UserForm (only two in the example...it can be expanded with defined dependencies) using Dynamic Named Ranges.

This sample has been extrapolated from this Thread which is based on Worksheet Comboboxeshttp://www.excelforum.com/excel-gene...down-list.html

**bojum01** · 06-25-2011, 07:15 PM

Thanks, I will work my way through the code and sample.
I have sorted the on worksheet formula. It is below for info.
'=OFFSET((INDIRECT(CONCATENATE("R",ROW(Data_Array_A_S)-1+MATCH(cbo_B18_Select,Data_Array_A_Valid_B18,0)-1+MATCH(cbo_B19_Select,Data_Array_A_Valid_B19,0),"C",COLUMN(Data_Array_A_S)+1+1),FALSE)),MATCH(cbo_B 23_Select,Data_Array_A_Valid_B23,0),1,COUNTIF(Data_Array_A_Valid_B23,cbo_B23_Select),1)

**royUK** · 06-26-2011, 03:24 AM

If you are using Dynamic Named Ranges I would suggest that names used correspond to the category. This simplifies the code & the controls can be populatedusing the List Property based on the selection in the first control.

In the example I have used ComboBoxes & ListBoxes, you can of course use a ComboBox to select from & populate a ListBox based on the selection.

**bojum01** · 06-27-2011, 07:47 PM

Thank you Jaslake and royUK. A combination of both codes and samples resolved my problem.
I had to add a little extra code to send the result to the workbook but this was just because I wanted to keep it all variables and not restrict the first level entry.
Having multiple levels of combo box makes it more complicated as each level is formulated on all the preceding selections.
I have noticed that it is not error tolerant but that is another level of sophistication that I will put in another thread when I am ready to go it that direction. It is good to get it working, and as long as all the base data for the lists is completed correctly, it should not give the error message. Eg. If all of the four levels are not available in a certain combination.
Thanks again.

bojum01

**jaslake** · 06-27-2011, 07:52 PM

Hi bojum01

Glad to be of help.

If that satisfies your immediate need, please mark your thread as "Solved".

To mark your thread solved do the following:
- Go to your first post on the thread
- Click edit
- Click Advance
- Just below the word "Title:" you will see a dropdown with the word No prefix.
- Change to Solved
- Click Save