+ Reply to Thread
Results 1 to 2 of 2

update data validation sources if validation values change

  1. #1
    Registered User
    Join Date
    04-10-2012
    Location
    Chicago
    MS-Off Ver
    Excel 2003
    Posts
    1

    update data validation sources if validation values change

    Forgive me for this long winded post, but I can't explain in any other way as this is a complex scenario

    We have an MC Access DB that holds the values for our data validations and we read these values into our excel at the time a sheet is opened with a macro.

    Basically, we have 3 columns that depend on the previous for the values that are selectable.

    Application
    Area (depends on what Application is selected)
    Function Name (depends on what Area is selected)

    The validation rules are set in Data Validation Source at creation time based on the values in the Access DB. Basically the data validation source value for Application is determined when user selects "Insert Test Step" button. It then Goes to the Access DB, gets all Applications, and populates the Source value for Data Validation by building a comma separated list from the DB values.

    When an Application is selected, Area (Column B) does the same, it goes to Access DB and gets all Areas associated to the Application selected in column A and builds the validation lists from the returned values, same for Function Name, only it is based on the value in Area (column B)

    This is all working fine, but when we add to the Access DB, say a new Area is defined for an Application, the sheets don't reflect that new value as the validations are not updated. We have a macro to reload the values, but would need to go cell by cell to update all the validations based on the new values.

    Anyone know of a way this can be done quickly/easily without having to iterate through each cell, save it's current value, update the validation source list, and then reselect the saved value?

    We have hundreds of saved sheets with the current test values saved, and when we add new DB entries (due to new functionality), updating the current sheets is very time consuming.

    I hope I described it well enough that it is clear what we need to do.

    Thanks in advance for any assistance.

  2. #2
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

    Re: update data validation sources if validation values change

    Quote Originally Posted by mauler69 View Post
    The validation rules are set in Data Validation Source at creation time based on the values in the Access DB. Basically the data validation source value for Application is determined when user selects "Insert Test Step" button. It then Goes to the Access DB, gets all Applications, and populates the Source value for Data Validation by building a comma separated list from the DB values.
    This suggestion wont fix your current sheets. It's intended for moving forward for new Data Validations (DV).

    Instead of "building a comma separated list" for the source list, put the list in a column somewhere, define a Dynamic Named Range for that list, and use the Named Range as the DV source. That way, when you update\change the Dynamic Named Ranges lists, all the DV lists automatically update.

    Create a Dynamic Named Range

    Create Dependent Drop Down Lists
    Surround your VBA code with CODE tags e.g.;
    [CODE]your VBA code here[/CODE]
    The # button in the forum editor will apply CODE tags around your selected text.

+ 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. Replies: 3
    Last Post: 05-09-2016, 08:27 PM
  2. Replies: 4
    Last Post: 07-03-2014, 02:37 AM
  3. Update Color Change of Object From Data Validation List
    By BobBing in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-09-2013, 06:58 PM
  4. [SOLVED] IF Function in Data Validation with Sources from other Worksheets
    By pandaman in forum Excel - New Users/Basics
    Replies: 5
    Last Post: 12-19-2010, 12:14 AM
  5. Replies: 2
    Last Post: 01-03-2010, 05:21 PM
  6. Data Validation-Different Sources
    By matt_the_brum in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 12-13-2006, 10:47 AM
  7. [SOLVED] Data Validation with Multiple Sources
    By Jerkyboy in forum Excel General
    Replies: 1
    Last Post: 08-15-2006, 05:55 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