Combo Box code for dependent data validation using a table for validation data

Hi everyone.

First, sorry, I've not made enough posts to be able to put links in here, so I'll try obfuscating them.

I have a lot of excel files with tables in multiple sheets populated from external sources, and then manually categorised, and subcategorised. As you can imagine, the categories and subcategories are using data validation lists. My dependent validation data is always stored as a table - the Categories are headers, and the subcategories are the columns. The data can be multiword, and sometimes uses the "&" symbol.

Given the limitations of the built-in drop-down menu, I'd like to use a Combo Box instead. I've never used VBA in Excel (although I've programmed in languages like FORTRAN, C++, Perl, etc. since the 70s).

Looking for dependent data validation list Combo Box code, I found this page "contextures.com / xldataval11.html"
and this thread "1194306-data-validation-dynamic-drop-down-with-combo-box-indirect-not-working.html" with code for the validation data being in named ranges, but nothing when the validation data is in a table. Here's the code from that post:

Please Login or Register  to view this content.

I'm including a sample file of the type of file I have. I'd appreciate any help in modifying the code to use validation data from a table instead of named ranges, so I can make minimal changes to the files (adding a Combo Box and code) with having to transform all of the validation tables into names ranges as well.

Originally Posted by Richard Buttrey

It seems quite complicated code if all you want to do is populate several combo boxes with values from several lists.

First of all don't reject the idea of using range names. Dynamic range names which expand or contract as new items are added or deleted will ensure that wherever you use the range name it will cover all your values. For example to create dynamic range for the Food create a name called say lst_Food and define it with the formula

=OFFSET(ValidationTable!$A$5,0,0,COUNTA(ValidationTable!$A5:$A100),1)

Then use the 'RowSource' property of the CombBox and enter the name 'lst_food'

The problem is that I have a lot of existing workbooks, often with multiple tables of data for data validation, so I'd really like to find a solution to this that allows me to minimize the job of updating those files.

Well, I learnt a useful bit of VBA code (Debug.Print) and found that the issue is not a difference of using named ranges vs tables, but limitations in the code to deal with complex data validation list formulas.

On such formulas, the code breaks at this point:

Please Login or Register  to view this content.

On the simple code used for taking the contents of the cell to the left as the name of a range, .ListFillRange ends up as an absolute range.

With the formulas I've been using for tables, however, the nm.RefersToRange.Parent.Name causes an issue.

A value of nm from a named range (with substitutes to create the name) that works at this point is

Please Login or Register  to view this content.

A table one that breaks has a value of nm at this point:

Please Login or Register  to view this content.

The main difference between them is the R1C1 indirect, but having embedded named formulas with also break it.

As a hack, the following validation list formula will work with the code for tables.

Please Login or Register  to view this content.

So, instead using an A1 reference instead of using R1C1 indirect, it is possible to use tables instead of named ranges.
For me, the advantage is that it is easier to maintain a table, rather than named ranges.

A more eloquent solution would be to modify the code so that it also bottoms out to an absolute range.