+ Reply to Thread
Results 1 to 3 of 3

Excel VBA, Applying Data Validation Dynamically

  1. #1
    Registered User
    Join Date
    10-03-2022
    Location
    USA
    MS-Off Ver
    19
    Posts
    2

    Question 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.
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    10-17-2007
    Location
    Tasmania, Australia
    MS-Off Ver
    2019, 365(v2403)
    Posts
    370

    Re: Excel VBA, Applying Data Validation Dynamically

    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.
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    07-21-2022
    Location
    USA
    MS-Off Ver
    2019
    Posts
    2

    Re: Excel VBA, Applying Data Validation Dynamically

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Applying Data Validation to all sheets
    By Goodnight666 in forum Excel General
    Replies: 1
    Last Post: 03-30-2022, 01:37 AM
  2. Applying a multiplier based on data validation selection
    By Rhuumz in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 11-26-2019, 09:24 PM
  3. [SOLVED] Issues with applying IF function to Data Validation List
    By g.costapinto in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 10-18-2019, 08:33 AM
  4. [SOLVED] Applying filtering on data validation selection
    By Franki81 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 04-05-2016, 07:09 AM
  5. [SOLVED] Applying Data Validation to Allow Only Formulas
    By loverfellow in forum Excel General
    Replies: 5
    Last Post: 12-03-2015, 12:32 AM
  6. [SOLVED] Applying custom data validation to multiple columns
    By D_N_L in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 04-21-2014, 05:11 AM
  7. Applying Conditional Formatting to Data Validation
    By kcstewart in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 10-28-2007, 06:46 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1