+ Reply to Thread
Results 1 to 2 of 2

Suppress drop-down list dependent on value chosen in another drop-down list

  1. #1
    Registered User
    Join Date
    08-30-2013
    Location
    Seattle, WA
    MS-Off Ver
    Excel 2010
    Posts
    11

    Suppress drop-down list dependent on value chosen in another drop-down list

    I am simplifying this post (and the attached file) from my earlier post in hopes of getting some help. I have been pounding my head against the wall for days on this, still can't get it to work.

    I modified some code from another person's post to try to achieve what I was looking to do:
    http://www.excelforum.com/excel-form...selection.html

    I have a table, there are multiple columns.
    I have two columns [Category] & [Detail], each of them has a drop down list.
    The values displayed in the drop down for [Detail] are dependent on what was chosen from the drop down in [Category].
    I have used the INDIRECT function to properly display the right list of values in the [Detail] column.

    I have a value in [Category], "Mineral" for which I do not want the drop down to appear in [Detail], all other values should activate the drop down list in [Detail] and invoke the INDIRECT function.

    I was able to use the code from the other post to get the dropdown suppressed in the first row of my table, and to get the INDIRECT call working for the valid values. (Please note this worksheet will eventually be protected, in order to get the INDIRECT call portion of the macro to work I had to "unlock" the cell first - via the macro)
    Can't figure out how to get it to work on the rest of the rows in the table (which can vary depending on if the user is adding or deleting rows).

    Including the code as I have modified it to work. E6 is the location of first row [Category], F6 is the location of first row [Detail]
    Also note, including the code which clears the info in [Detail] should the value of [Category] change.

    Please Login or Register  to view this content.
    I am attaching a sample file.
    The password to unprotect, is "orderform"

    thanks!
    Attached Files Attached Files
    Last edited by ptmald; 10-15-2013 at 03:34 PM. Reason: simply from original post

  2. #2
    Registered User
    Join Date
    08-30-2013
    Location
    Seattle, WA
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Suppress drop-down list dependent on value chosen in another drop-down list

    I figured this one out. The code above has changed to:
    Please Login or Register  to view this content.
    No longer able to call the INDIRECT function, each option must be explicitly specified in the macro. This works fine for a smaller dependent list, but could be problematic for a longer list, I'm sure there is a more elegant way to solve this, but this works for me. Hopefully someone else looking for similar functionality will benefit from my labors on this one.
    functioning solution attached.
    Attached Files Attached Files

+ 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. Moving Rows to another Sheet chosen from a drop down list
    By RumaR28 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-25-2013, 07:32 AM
  2. Adding a value depending on Drop-down-list chosen value
    By freaken00 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-23-2013, 05:24 AM
  3. [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
  4. [SOLVED] 2nd Drop Down List Dependent on 1st Drop Down List Creation
    By LWABowler in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-21-2012, 03:33 AM
  5. [SOLVED] My Excel drop-down list eliminates from list options chosen. Help
    By Sybil in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-19-2006, 05:25 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