+ Reply to Thread
Results 1 to 4 of 4

Clearing out data validation lists when another changes?

  1. #1
    Registered User
    Join Date
    12-18-2009
    Location
    US
    MS-Off Ver
    Excel 2003
    Posts
    2

    Clearing out data validation lists when another changes?

    I am trying to clear multiple validation lists if one of two particular lists changes, but can't find the code to do this.

    Basically, I want the following cells (which are each independent validation lists) to reset back to being 'blank' (B14,B15,B16,B17,B18,B19,B20,B21,B22,B26,B27,B28,B29,B30,B31,B32,B33,B34,B35,B36,B37) if either of two cells change (B20,B21).

    If B20 changes, I also need it to 'reset' B21 to blank - but if B21 changes, B20 should not reset.

    I hope this wasn't confusing, but appreciate your help.

    This file will also end up being protected, so I need code that will this macro to run even when the sheet is protected.

    Thanks
    Brian

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,425

    Re: Clearing out data validation lists when another changes?

    You need a worksheet change event to monitor the two cells and take appropriate action as outlined. As you'll be changing cells on the sheet that is being monitored, you'll need to use Application.EnableEvents=False before and Application.EnableEvents=True after the change(s).

    If the sheet is protected and you want to make changes to locked cells, then you'll need to Unprotect before and Protect after the change.

    For a definitive answer, please post a sample workbook.

    Regards, TMS
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Registered User
    Join Date
    12-18-2009
    Location
    US
    MS-Off Ver
    Excel 2003
    Posts
    2

    Re: Clearing out data validation lists when another changes?

    Thanks TMS,

    Attached is a dumbed down sample of what I am trying to do.

    Basically, I want everything the 'reset' if A4 changes - basically go back to a blank cell. If B4 changes, I want C4 & D4 to 'rese't, but A4 to remain as the current input.

    C4 and D4 should be able to change without 'reseting' any thing; they are independent of each other and the values of these cells are determined based on the value of B4.

    In my real application, I have a lot more of the C4/D4 cells but figure this sample gives the general idea.

    Thanks again for the help!

    Brian
    Attached Files Attached Files

  4. #4
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,425

    Re: Clearing out data validation lists when another changes?

    Please Login or Register  to view this content.


    Regards, TMS

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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