Another Dependent Data Validation Combobox

Hi everyone,

I'm still curious about the sample from Contextures, http://www.contextures.com/excelfiles.html (DV0032 - Dependent Data Validation Combobox)

I have made a modification from its file (sample is attached). First drop list can be autocomplete, but the second cannot. For the comparing, I made row 2 in ValidationSample sheet is my modification, and row 3 is the original.

Can you guys help me find the solution? thank you before..

I'm not sure if/how you changed the code.. If I paste back the code that was given in the contextures site.. it works for me.

I'm not sure.. I am not enough of a VBA expert to tell you how to change the Contextures supplied code to accomodate Indirect references to lists...

If you enter the list name directly..it works.. see for example cell C3.. I change data validation formula to =Finance and it works.. but if you indirectly refer to the list with =Indirect(B3)... it does not work....

so I think the code needs modifying... but can't figure it out..

...anyone with VBA expertise can help ??

The issue is that your named range for row 2 (unlike the Contextures examples) does not correlate exactly to the value chosen in B, you have accounted for this already in native XL via the Validation List using:

=INDIRECT("Title"&SUBSTITUTE(B2," ","")

You must therefore apply the same logic to the VBA... the ComboBox is populated here:

Please Login or Register  to view this content.

this then needs to be adapted such that the Named Range string is correct, ie mimic your INDIRECT validation formula such that it becomes:

Please Login or Register  to view this content.

Obviously you run into problems if your naming conventions are not consistent...

Hey, it worked! now I understand a little bit about the script hehe

You only need to change which column, str value, and offset value..

Thanks DonkeyOte and NBVC

I tested the sample file(s) in 2007 so I would disagree with your friend I'm afraid...I suspect there are other underlying problems.

I'm afraid I don't store the files unless I upload here ... the code you had with the advised revision should work without incident on XL2007.