Excel VBA, Applying Data Validation Dynamically

Hello Everyone.

I'm optimistic that there's a solution to my issue. I have a dataset as the source (table named "t_Src") and a user input table (table named "t_DV"). The goal is to automatically apply data validation to the User Input Table's Databodyrange as the user enters or selects from a dropdown. When nothing is selected in the User Input table, any data validation dropdown should include all options from the source table. However, as soon as the user selects an option from any column, the other columns in the user input table should be re-validated to show only options that match the user's selection. As the user continues to select criteria from any other columns, the data validation should change accordingly. Additionally, if the user deletes any selected criteria, the data should be re-validated for the remaining columns.

I've managed to achieve this objective using the Advanced Filter function, but it's a bit slow. I also want to avoid looping through the table/array as that also slows down the code. I'm wondering if you have any ideas on how to achieve this faster and more efficiently.
I have the Excel file attached here as well.

By the way, if there is way without using VBA i would like to learn about it as well.

Thank you in advance for your help and time.

Hi Alex,

This is what I have come up with after a number of tries. Can you have a look at the attached and see if it is right. Looks good to me.

I used this thread as a template
https://www.excelforum.com/excel-gen...-per-cell.html

Please Login or Register  to view this content.

Thank you so much for your time and help, the Provided solution is working only if the user starts selecting the OD first and then selects the other criteria, and also when the selected criteria are deleted, it doesn't re-adjust the data validation list to the new combination.

I have attached an Excel file with Macro and the solution I have come up with, the Input sheet can take any criteria in any order.

Thank you again for your Time and Help,
PE ToolBox_Csg Data Validation_15_New Method 2_Question.xlsm