+ Reply to Thread
Results 1 to 9 of 9

Repeating dependent ComboBoxes in a Userform

  1. #1
    Registered User
    Join Date
    01-26-2016
    Location
    Houston, Texas
    MS-Off Ver
    2010
    Posts
    4

    Repeating dependent ComboBoxes in a Userform

    Hello,

    I have a pair of dependent ComboBoxes in a user form. ComboBox1 and ComboBox2. ComboBox2 is dependent on ComboBox1.

    This pair appears several times in the user form in the form of (ComboBox3 and ComboBox4), (ComboBox5 and ComboBox6), ..etc. next to a text box which contains a description of machinery condition. The user has to use the ComboBoxes only in the case of a failure to the piece of machinery.

    I am currently using in the attachment.

    This code is sufficient only if there is a single pair of dependent ComboBoxes. Is there way to extend this code for all ComboBoxes such that it is called only when a pair of ComboBoxes is used.

    Thank you in advance.

    ef1.txt

  2. #2
    Forum Expert
    Join Date
    07-31-2010
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    4,070

    Re: Repeating dependent ComboBoxes in a Userform

    You are going to need to code each combobox pair in a similar fashion as what you submitted.
    If you are happy with my response please click the * in the lower left of my post.

  3. #3
    Registered User
    Join Date
    01-26-2016
    Location
    Houston, Texas
    MS-Off Ver
    2010
    Posts
    4

    Re: Repeating dependent ComboBoxes in a Userform

    Hi stnkynts. Thanks for your reply.

    The data in all the pairs is identical. Do I still need to do this? Sorry, I am new to VBA.

    Thanks!

  4. #4
    Forum Expert
    Join Date
    07-31-2010
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    4,070

    Re: Repeating dependent ComboBoxes in a Userform

    You should be able to just copy and paste the code from the ComboBox1_Change event to the CombboBox3_Change event and the ComboBox5_Change event. You will then need to change the associated combobox that affects it (i.e. ComboBox2 to 4, etc).

    You will also want to amend the UserForm_Initialize event so that it properly populated your combobox1, 3, and 5 (see below code)
    Please Login or Register  to view this content.
    Example:
    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    01-26-2016
    Location
    Houston, Texas
    MS-Off Ver
    2010
    Posts
    4

    Re: Repeating dependent ComboBoxes in a Userform

    Yes, I realize that but there are atleast 50 pairs of dependent comboboxes. I guess I was just hoping that there was a way to add a module that could be called in each Combobox_Change() function.

  6. #6
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,907

    Re: Repeating dependent ComboBoxes in a Userform

    The Initialize part can be shortened like this.
    Please Login or Register  to view this content.
    The rest will take a look later.

  7. #7
    Forum Guru mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,230

    Re: Repeating dependent ComboBoxes in a Userform

    Over fifty pairs, that calls for a Class Module
    Add a Class Module. Use the Properties Window to change its name to clsPairedDependents. Then put this code into the class's code module.
    Please Login or Register  to view this content.
    In this Demo user form, I used only 6 Comboboxes and made ComboBox1 the Master box for Combobox2, Combobox3 masters ComboBox4, etc.
    The loop in the Initialize event will have to be modified to match the pairings that you want.
    Notice that changing either of the paired boxes will 1) make that pair the ActiveDependentPair and fire the ActiveDependentPair_Change event.
    In this demo form, the ActiveDependentPair_Change event sets the .Caption of a Label to show some of the properties of clsDependentPair. (The Object Browser will show all of those properties, they mirror the properties of a ComboBox)

    Depending on if the Master or the Dependent of the pair caused the Change, either the ActiveDependentPair_MasterChange ActiveDependentPair_DependentChange will fire after the _Change event (but never both)

    Please Login or Register  to view this content.
    Attached Files Attached Files
    _
    ...How to Cross-post politely...
    ..Wrap code by selecting the code and clicking the # or read this. Thank you.

  8. #8
    Registered User
    Join Date
    01-26-2016
    Location
    Houston, Texas
    MS-Off Ver
    2010
    Posts
    4

    Re: Repeating dependent ComboBoxes in a Userform

    Thanks mikerickson. Its going take me a bit to wrap my head around this. I will try it this weekend and post back. Thank you again!

  9. #9
    Forum Guru mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,230

    Re: Repeating dependent ComboBoxes in a Userform

    The Creeping Feature Creature overcame me on that version. It got a bloated and cumbersome. This is leaner.

    The custom Class clsDependentPairs has properties:

    MasterBox
    '-- the control that determines which DependentList is shown in the DepenedentBox.
    '-- MasterBox can be either a ComboBox or a ListBox

    DependentBox
    '-- the control that shows the DependentList, depending on which item is selected from the MasterBox.
    '-- DependentBox can be either a ComboBox or a ListBox

    MasterList is a pass-through property that acts like .MasterBox.List
    DependentList(index) is what is shown when MasterBox.ListIndex = Index.
    '-- Typicaly DependentList(index) is a one dimensional array.
    '-- If DependentList(index) = Null, then the DependentBox will be cleared when MasterBox.ListIndex = Index
    '-- If DependentList(index) = "nc" or "No Change" (or any string), setting MasterBox.ListIndex to index will not change the list in DependentBox.
    '-- index can be -1. DependentBox will show DependentList(-1) when nothing is selected in the MasterBox.

    Note that either the MasterBox or the DependentBox can be multi-column. They cannot be multi-select.

    It will also pass a Change event to the (optional) ChangedPair variable in the Userform. The DependentChanged argument will be True/False depending on whether the ChangedPair.DependentBox or ChangedPair.Masterbox was changed.

    In the attached, the Userform_Intialize event shows one way to match master controls with their dependent control.
    The ChangedPair_Change event fills Label1 with information.

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by mikerickson; 01-30-2016 at 03:32 PM.

+ 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: 13
    Last Post: 10-20-2019, 05:39 AM
  2. [SOLVED] Dependent ComboBoxes in Userform, then Submit Button
    By markusvirus in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 12-04-2015, 04:58 PM
  3. Two comboBoxes dependent on one another (userform)
    By fionamb83 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-20-2013, 05:18 AM
  4. [SOLVED] Building dependent comboboxes on an existing userform
    By Sway1978 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-19-2013, 11:11 AM
  5. [SOLVED] Dependent comboboxes
    By ObiWanBaloney in forum Excel General
    Replies: 6
    Last Post: 06-04-2012, 08:40 PM
  6. dependent comboboxes
    By humboldtguy in forum Excel Programming / VBA / Macros
    Replies: 15
    Last Post: 05-14-2010, 10:40 PM
  7. UserForms & dependent ComboBoxes?
    By hit_king51 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 05-11-2010, 09:30 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