+ Reply to Thread
Results 1 to 11 of 11

Drop down dependents on each other in form VBA

  1. #1
    Forum Contributor
    Join Date
    08-10-2016
    Location
    Pakistan
    MS-Off Ver
    365
    Posts
    242

    Drop down dependents on each other in form VBA

    I need a VBA code which should do the following operation:
    1) These drop down boxes should extract data from their respective column from "sheet1".
    2) 5th drop down is dependent on 4th drop down(example: if Feeding is selected in 4th combo box, it should only show those items in 5th drop down which are in front of Feeding in next column and so on.
    3) 6th combo box is dependent on 5th drop down box)example: if FE-101 (Feeding & Blending Conveyor) is selected in 5th drop down box, then in 6th drop down only those items will come which are in front of FE-101 (Feeding & Blending Conveyor) in next column and so on.
    4) 7th drop down is dependent on 4th or 5th drop down( example: if FE-101 (Feeding & Blending Conveyor) is selected in 5th drop down box then in 7th drop down it should show those items only which are in front of FE-101 (Feeding & Blending Conveyor) in 2nd next column(Column G)
    5) The other drop down boxes which are dropdowns 1, 2, 3 and 8 are not dependent, they should extract all data from their respective column

    data pic.PNG form pic.PNG
    Attached Files Attached Files

  2. #2
    Forum Expert torachan's Avatar
    Join Date
    12-27-2012
    Location
    market harborough, england
    MS-Off Ver
    Excel 2010
    Posts
    4,295

    Re: Drop down dependents on each other in form VBA

    Give the attached a try.
    For the filtering to work column 'I' of the table must be entered as 'text' not numeric.
    for ease I have just prefixed the numbers with an apostrophe.
    torachan.
    Attached Files Attached Files

  3. #3
    Valued Forum Contributor
    Join Date
    06-22-2018
    Location
    Blackpool, England
    MS-Off Ver
    2019
    Posts
    408

    Re: Drop down dependents on each other in form VBA

    While torachan's uploaded a proposed solution I've been having a look, too. So I'll attach it anyway.

    The key is to populate the combo-boxes on the fly and to add in the requisite conditions to add them to the list. The attached also checks for duplicates in the comboboxes...

    Have a look at the code so you can see what's going on - you'll be able to adapt it to suit your future needs, too.

    HTH
    Tim
    Attached Files Attached Files
    Never stop learning!
    <--- please consider *-ing !

  4. #4
    Forum Expert torachan's Avatar
    Join Date
    12-27-2012
    Location
    market harborough, england
    MS-Off Ver
    Excel 2010
    Posts
    4,295

    Re: Drop down dependents on each other in form VBA

    @harrisonland
    I have not look in depth as to the reason, your 'Equipement Sub-Assembly' does not filter out accurately by using the 'TAO code'.
    Follow my code through you will also see the combos are loaded 'on the fly' using a very compact code.
    torachan

  5. #5
    Valued Forum Contributor
    Join Date
    06-22-2018
    Location
    Blackpool, England
    MS-Off Ver
    2019
    Posts
    408

    Re: Drop down dependents on each other in form VBA

    Thanks, torachan.... thicko here had completely omitted to filter, that's why it wasn't filtering by TAO code! Should have read
    Please Login or Register  to view this content.
    I've had a look at your code... I really need to become more confident using arrays - I just have a habit of not using them, although I know they're often more efficient. Using named tables, of course, also makes the whole thing more efficient.

    Please don't take this the wrong way, but I find the lack of spacing in your code makes it a bit hard on the eye.

    Anyway, thanks for pointing out my error!

    Tim

  6. #6
    Forum Expert torachan's Avatar
    Join Date
    12-27-2012
    Location
    market harborough, england
    MS-Off Ver
    Excel 2010
    Posts
    4,295

    Re: Drop down dependents on each other in form VBA

    Fair criticism, just my style, difficult to change after 50 years programming, it would confuse my limited brain cells.
    The main thing is we can all learn from each other giving us all a broader knowledge.
    Attached a prettier picture of the code.
    Attached Files Attached Files

  7. #7
    Forum Contributor
    Join Date
    08-10-2016
    Location
    Pakistan
    MS-Off Ver
    365
    Posts
    242

    Re: Drop down dependents on each other in form VBA

    @harrisonland Thank you .. there is a little change required, 1) as i already told that column C for 3rd combo box should not filter and will contain all the stuff from columns C.
    Last edited by Zahid0111; 03-29-2020 at 04:28 AM.

  8. #8
    Forum Contributor
    Join Date
    08-10-2016
    Location
    Pakistan
    MS-Off Ver
    365
    Posts
    242

    Re: Drop down dependents on each other in form VBA

    @torachan Thank you so much..this works fantastic...

  9. #9
    Forum Contributor
    Join Date
    08-10-2016
    Location
    Pakistan
    MS-Off Ver
    365
    Posts
    242

    Re: Drop down dependents on each other in form VBA

    @torachan if i want to transfer this code to my sheet, is it compulsory to make the data in tabular form and declare the columns as table 1, 2 and so on ??

  10. #10
    Forum Expert torachan's Avatar
    Join Date
    12-27-2012
    Location
    market harborough, england
    MS-Off Ver
    Excel 2010
    Posts
    4,295

    Re: Drop down dependents on each other in form VBA

    Attached file using your data as ranges.
    Tables are just my preferred way of dealing with pseudo database apps.
    They lend themselves better to dynamic self maintenance.
    If you develop your app further (i.e. data entry/maintenance by UserForm the Table is more versatile).
    Attached Files Attached Files

  11. #11
    Forum Contributor
    Join Date
    08-10-2016
    Location
    Pakistan
    MS-Off Ver
    365
    Posts
    242

    Re: Drop down dependents on each other in form VBA

    Thank so much...understood...

+ 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. [SOLVED] Dependent Drop Down Lists, Data Validation, Reset Dependents
    By daxlyon in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-12-2013, 11:15 AM
  2. Drop-down Form Field launching another form
    By aCmE in forum Word Formatting & General
    Replies: 0
    Last Post: 12-23-2010, 08:56 AM
  3. [SOLVED] Highlight cells which have no dependents or duplicate dependents
    By Ali-imran in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 12-08-2009, 09:02 AM
  4. A Form and Drop Down Box
    By cakonopka in forum Excel General
    Replies: 3
    Last Post: 03-14-2008, 05:04 AM
  5. controlling a form drop down based on selection of another drop down
    By flurry in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-05-2006, 11:31 AM
  6. Form Drop Down
    By Jim Heavey in forum Excel General
    Replies: 1
    Last Post: 02-06-2006, 06:00 PM
  7. [SOLVED] Dependents
    By kt in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-10-2005, 11:05 PM
  8. Dependents
    By Geoff in forum Excel General
    Replies: 0
    Last Post: 06-10-2005, 12:05 AM

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