+ Reply to Thread
Results 1 to 10 of 10

Clear up to three dependent drop down lists based on selection in superior drop down list

  1. #1
    Registered User
    Join Date
    04-11-2012
    Location
    Nashville, TN
    MS-Off Ver
    Excel 2010
    Posts
    76

    Clear up to three dependent drop down lists based on selection in superior drop down list

    Hello all,

    In the attached workbook I have four hierarchical drop down lists named Region, Zone, District, and BAC, with Zone, District, and BAC all dependent on the selection in the previous field. I am looking for the VBA to clear any dependent list selections when a different superior selection has been made.

    E.g.: if there are selections made in all four lists, and then Region is changed, the bottom three are cleared.
    Or, if there are selections made in all four lists, and then Zone is changed, the bottom two are cleared and Region remains.
    Or, if there are selections made in all four lists, and then District is changed, just BAC is cleared and Region and Zone remain.

    Any help would be greatly appreciated!
    Attached Files Attached Files

  2. #2
    Forum Guru karedog's Avatar
    Join Date
    10-03-2014
    Location
    Indonesia
    MS-Off Ver
    2003
    Posts
    2,971

    Re: Clear up to three dependent drop down lists based on selection in superior drop down l

    Maybe :

    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    04-11-2012
    Location
    Nashville, TN
    MS-Off Ver
    Excel 2010
    Posts
    76

    Re: Clear up to three dependent drop down lists based on selection in superior drop down l

    Works like a charm! Thanks @karedog.

  4. #4
    Forum Guru karedog's Avatar
    Join Date
    10-03-2014
    Location
    Indonesia
    MS-Off Ver
    2003
    Posts
    2,971

    Re: Clear up to three dependent drop down lists based on selection in superior drop down l

    You are welcome, thanks for the rep. points.


    Regards

  5. #5
    Registered User
    Join Date
    04-11-2012
    Location
    Nashville, TN
    MS-Off Ver
    Excel 2010
    Posts
    76

    Re: Clear up to three dependent drop down lists based on selection in superior drop down l

    As a follow up, could this same thing be achieved without VBA? This functionality may be needed for a deliverable where the recipients may not have macros enabled.

  6. #6
    Forum Guru karedog's Avatar
    Join Date
    10-03-2014
    Location
    Indonesia
    MS-Off Ver
    2003
    Posts
    2,971

    Re: Clear up to three dependent drop down lists based on selection in superior drop down l

    No, it can't.

    Without VBA (VBA subs or VBA UDFs), the only way to detect any changes in other cell is by using formulas (for example "=SUM(M3,M4)" , when M3 or M4 changed, the formula is recalculated). But we can't put formulas as validation list's member, so by design, this cannot be achieved. You must use VBA for this purpose.

    To force the client to enable macro, you can google using these keywords :
    excel vba force macros enabled

  7. #7
    Registered User
    Join Date
    04-11-2012
    Location
    Nashville, TN
    MS-Off Ver
    Excel 2010
    Posts
    76

    Re: Clear up to three dependent drop down lists based on selection in superior drop down l

    Hello all,

    Resurrecting this post because I now have a sheet with two sets of dropdowns, independent of each other. I was unable to adapt the vba code that @karedog provided to accommodate two different sets of hierarchical dropdowns. Looking to have the same outcome for both sets of dropdowns - clearing dependent lists when a superior selection has been changed. I've attached an updated workbook.

    Thanks ahead for your help!
    Attached Files Attached Files

  8. #8
    Forum Guru karedog's Avatar
    Join Date
    10-03-2014
    Location
    Indonesia
    MS-Off Ver
    2003
    Posts
    2,971

    Re: Clear up to three dependent drop down lists based on selection in superior drop down l

    Just duplicate existing code and adjust it to new added range :

    Please Login or Register  to view this content.

  9. #9
    Registered User
    Join Date
    04-11-2012
    Location
    Nashville, TN
    MS-Off Ver
    Excel 2010
    Posts
    76

    Re: Clear up to three dependent drop down lists based on selection in superior drop down l

    Thanks again @karedog!

  10. #10
    Forum Guru karedog's Avatar
    Join Date
    10-03-2014
    Location
    Indonesia
    MS-Off Ver
    2003
    Posts
    2,971

    Re: Clear up to three dependent drop down lists based on selection in superior drop down l

    You are welcome, glad to help.


    Regards

+ 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: 5
    Last Post: 11-11-2013, 10:23 AM
  2. [SOLVED] Return value based on 2 dependent drop down lists
    By arsene2conde in forum Excel Formulas & Functions
    Replies: 19
    Last Post: 10-14-2013, 09:44 AM
  3. [SOLVED] The VBA code to auto-clear dependent drop down selection when parent cell change.
    By Hotgirl in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 10-29-2012, 07:48 PM
  4. [SOLVED] Column B drop down list with 2 options, columns C & E with dependent drop down lists
    By betic in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-26-2012, 03:47 PM
  5. [SOLVED] Dynamic Ranges as drop down lists based on another drop down list
    By Excel_Beginner_1 in forum Excel General
    Replies: 4
    Last Post: 05-15-2012, 03:31 PM
  6. IF THEN ELSE based on Three Dependent Drop Down Lists (VBA)
    By vtsoldier2010 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-15-2012, 09:56 AM
  7. [SOLVED] Clear Dependent Drop Down Lists
    By Ambassador777 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-03-2012, 11:38 PM

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