+ Reply to Thread
Results 1 to 7 of 7

Dependent Drop down list with DEPENDENT DEFAULT VALUE (e.g. Blank)

  1. #1
    Registered User
    Join Date
    04-25-2012
    Location
    Germany
    MS-Off Ver
    Excel 2010
    Posts
    5

    Dependent Drop down list with DEPENDENT DEFAULT VALUE (e.g. Blank)

    Hello, all

    There was a similar example given in the forum with Country (US, Canada) and Regions. As a solution the VBA code was provided.

    ************************************************************
    Please Login or Register  to view this content.
    *************************************************************
    I am more or less struggeling with adopting the code to my specific case.

    Problem Statement:
    I have two drop down lists 1) Finance Bucket and 2) Finance Request Type

    Finance Bucket has list: [GENERAL, SPECIFIC]

    If "GENERAL" is selected, [Warranty, Warranty_LU] is listed in the 2nd drop down list
    If "SPECIFIC" is selected, [WARRANTY_NU, WARRANTY_RS] is listed in the 2nd drop down list

    I am just using some dummy wordings due to privacy reasons.

    Scenario:
    First, I select "GENERAL" and choose the Finance Request Type to "Warranty". This is correct!
    Next I change the Finance Bucket to "SPECIFIC" and forget to update the Finance Request Type to either WARRARY_NU or WARRANTY_RS

    Question:
    If the primary list is changed, how can I make the 2nd drop down list to show a default value (e.g. blank)?
    Could someone help? I have a serious problem with this issue. Example EXCEL file is attached below


    Thanks in advance
    Attached Files Attached Files
    Last edited by davesexcel; 04-25-2012 at 08:31 PM. Reason: Code tags required when showing VBA code, please read the forum rules

  2. #2
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,345

    Re: Dependent Drop down list with DEPENDENT DEFAULT VALUE (e.g. Blank)

    Besides making the Cell blank, you can with code automatically drop the next cell when you change H3
    Please Login or Register  to view this content.
    Place this in your worksheet module, select the dropdown in H3 and H4 list will automatically drop down.

  3. #3
    Registered User
    Join Date
    04-25-2012
    Location
    Germany
    MS-Off Ver
    Excel 2010
    Posts
    5

    Red face Re: Dependent Drop down list with DEPENDENT DEFAULT VALUE (e.g. Blank)

    Wow, this is even more appropriate than entering Blank. It jumps to the next dropdown automatically and selects it.
    I actually have three levels of dropdown and will adapt the code accordingly.
    Let me try this out and i will get back to you.
    VM Obliged

  4. #4
    Registered User
    Join Date
    04-25-2012
    Location
    Germany
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Dependent Drop down list with DEPENDENT DEFAULT VALUE (e.g. Blank)

    This worked now with two dependant levels. Could you please help as to how i would need to change the script for three dependant levels? This is a need.


    Other than that how could i use specific names for the cells.
    The reason for asking this is, that currently i am referring tp $H$3. However, i may need to add/delete further rows/cells. I would then have to adjust the VB script.
    This is not an issue with one dropdown, however when i would have like 10, searching the right variables in VB script could be not trivial. Would be great, if you could propose something. But this is Nice to have!

    Appended is the spreadsheet with three dependant levels.

    Thanks!
    Attached Files Attached Files

  5. #5
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    7,676

    Re: Dependent Drop down list with DEPENDENT DEFAULT VALUE (e.g. Blank)

    Pl see the attached file.
    Worksheet_change event used for "Item" Sheet.
    Validation condition for H5 changed .ie If H4 is blank no drop down list for H5.
    You can extend the code for how many levels you want.
    Attached Files Attached Files
    Last edited by kvsrinivasamurthy; 04-26-2012 at 04:16 AM.

  6. #6
    Registered User
    Join Date
    04-25-2012
    Location
    Germany
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Dependent Drop down list with DEPENDENT DEFAULT VALUE (e.g. Blank)

    I have implemented it and learnt something new again!

    Now i know am asking for more but could i have the automatic drop down of the list as shown by "davexcel". I think thats more convenient.

    VB code is already provided by "davexcel" but needs to be adapted for the third level.

    I have appended the three dependend level file with the code for two levels already in there.

    Thanks Again,
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    04-25-2012
    Location
    Germany
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Dependent Drop down list with DEPENDENT DEFAULT VALUE (e.g. Blank)

    Team,

    I combined both the codes that were provided by you both to the following:

    Please Login or Register  to view this content.
    This has now provided me with more than i was looking for!

    I really appreciate the willingness shown here for helping others. Very much obliged!
    Last edited by davesexcel; 04-26-2012 at 08:06 AM.

+ 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