+ Reply to Thread
Results 1 to 17 of 17

Dependent comboboxes on userform

Hybrid View

  1. #1
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,892

    Dependent comboboxes on userform

    Hello everyone
    I have a code in userform that enables me to get dependent comboboxes on userform
    In my sheet I have Columns C & D and E that should be related to three comboboxes ..
    In userform initialize I got the unique values of column C in combobox1 and that's ok ..
    If I clicked combobox2 I should have a unique list of related items of combobox1 and that's ok too

    My problem is with combobox3 ..as I have all unique values for that item ..I need just to get unique values for that item based on both combobox1 and combobox2 ..

    for example:
    If I select "Cairo" in combobox1
    I got "Schools" only iin combobox2 and that's ok
    I expect to get Place10 to place17 only in combobox3 (but I got place10 to place22)
    Hope it is clear
    Attached Files Attached Files
    < ----- Please click the little star * next to add reputation if my post helps you
    Visit Forum : From Here

  2. #2
    Forum Expert
    Join Date
    08-16-2015
    Location
    Antwerpen, Belgium
    MS-Off Ver
    2007-2016
    Posts
    2,380

    Re: Dependent comboboxes on userform

    in Combobox2_Click
    Call test
    Sub test()
    Dim lr As Long, x As Long
    With Sheets("sheet1")
    lr = .Range("B" & Rows.Count).End(xlUp).Row
        For x = 1 To lr
            If .Range("C" & x) = ComboBox1 And .Range("D" & x) = ComboBox2 Then
                ComboBox3.AddItem (.Range("E" & x))
             End If
        Next
    End With
    End Sub
    Kind regards
    Leo
    Attached Files Attached Files

  3. #3
    Forum Expert Tinbendr's Avatar
    Join Date
    06-26-2012
    Location
    USA
    MS-Off Ver
    Office 2010
    Posts
    2,138

    Re: Dependent comboboxes on userform

    Here's my effort.
    Attached Files Attached Files
    David
    (*) Reputation points appreciated.

  4. #4
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,892

    Re: Dependent comboboxes on userform

    Thank you very much Mr. LeoTaxi for great help
    Yes that's exactly what I need
    Now how can I get the description in the textbox after change in combobox3...?

    Thanks a lot Mr. Tinbendr for help .. you helped me in another point which is the textbox ..
    But the main problem with combobox3 still as it
    I tried to use your solution related the Description but I failed. Can you show me how to apply this point on Tinbedr's file?
    Best Regards
    Last edited by YasserKhalil; 02-15-2016 at 08:27 PM.

  5. #5
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,892

    Re: Dependent comboboxes on userform

    Any help related the Description textbox ..?

  6. #6
    Forum Expert Tinbendr's Avatar
    Join Date
    06-26-2012
    Location
    USA
    MS-Off Ver
    Office 2010
    Posts
    2,138

    Re: Dependent comboboxes on userform

    Increase the column count of Combobox3 to 2.

    Store the row number of the match to Column 1. (Columns start at 0.)

    In the combobox3 change event, use the stored row number to present the record to the Desc Box.
            If .Range("C" & x) = ComboBox1 And .Range("D" & x) = ComboBox2 Then
                ComboBox3.AddItem (.Range("E" & x))
                Combobx3.Column(1, Combobox3.Listcount -1) = X
             End If

  7. #7
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,892

    Re: Dependent comboboxes on userform

    Thank you very much for helping me again
    In fact I tried to follow your steps and put the code in ComboBox3 change but don't know what x variable refer to?
    I tried this but got an error
    Private Sub ComboBox3_Change()
        
    
    If Me.ComboBox3.ListCount <> 0 Then
        'We stored the row number the data came from in combobox2 change.
        'We'll use it to display the description.
        Dim x As Long
        x = Me.ComboBox3.List(Me.ComboBox3.ListIndex, 1)
        
        If Range("C" & x) = ComboBox1 And Range("D" & x) = ComboBox2 Then
                ComboBox3.AddItem (Range("E" & x))
                ComboBox3.Column(1, ComboBox3.ListCount - 1) = x
             End If
        Me.TextBox_mycode = Cells(x, 6)
    End If
    End Sub
    Can you post the whole code for me?
    Thanks advanced for help
    Last edited by YasserKhalil; 02-16-2016 at 07:58 AM.

  8. #8
    Forum Expert Tinbendr's Avatar
    Join Date
    06-26-2012
    Location
    USA
    MS-Off Ver
    Office 2010
    Posts
    2,138

    Re: Dependent comboboxes on userform

    What is my example not doing?

  9. #9
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,892

    Re: Dependent comboboxes on userform

    Hello Mr. Tinbendr
    Please revise these lines again
    for example:
    If I select "Cairo" in combobox1
    I got "Schools" only iin combobox2 and that's ok
    I expect to get Place10 to place17 only in combobox3 (but I got place10 to place22)
    Hope it is clear
    As for your post 3 is working as for textbox but doesn't solve the main problem which is not to list all the places in combobox3 .. Just list the places related to "Sector" AND "Tasneef" ..so as in my example it should list onle "Place 10 to Place 17" in combobox3
    Hope it is clear

  10. #10
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,892

    Re: Dependent comboboxes on userform

    Every point is solved well separately but I couldn't apply the two points together

  11. #11
    Forum Expert Tinbendr's Avatar
    Join Date
    06-26-2012
    Location
    USA
    MS-Off Ver
    Office 2010
    Posts
    2,138

    Re: Dependent comboboxes on userform

    OK, I think I've got it now.
    Attached Files Attached Files

  12. #12
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,892

    Re: Dependent comboboxes on userform

    Thanks a lot Mr. Tinbendr for this great help and it is working well as expected exactly
    But is there a way to abbreviate the code as I noticed in the combobox2 change you used most of the lines in the sub
    Sub cValues(Txt As String, Obj As Object, Col As Integer)
    Can the previous sub include the code in combobox2 change to avoid redundancy?
    Thanks a lot for supporting and helping

  13. #13
    Forum Expert Tinbendr's Avatar
    Join Date
    06-26-2012
    Location
    USA
    MS-Off Ver
    Office 2010
    Posts
    2,138

    Re: Dependent comboboxes on userform

    No, since they perform different tasks.

  14. #14
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,892

    Re: Dependent comboboxes on userform

    Thanks a lot for help ..
    I will try understand the structures so as to simplify the code a little and if I could do that I will post again
    Best regards

  15. #15
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,892

    Re: Dependent comboboxes on userform

    Any suggestions about how to abbreviate the code provided in post 11?

  16. #16
    Forum Expert
    Join Date
    08-16-2015
    Location
    Antwerpen, Belgium
    MS-Off Ver
    2007-2016
    Posts
    2,380

    Re: Dependent comboboxes on userform

    Maybe like this ?


    Kind regards
    Leo
    Attached Files Attached Files

  17. #17
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,892

    Re: Dependent comboboxes on userform

    Mr. LeoTaxi
    Thank you very much for this great and wonderful solution
    You have solved it in an easy and perfect way ...
    Best 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: 13
    Last Post: 10-20-2019, 05:39 AM
  2. Repeating dependent ComboBoxes in a Userform
    By Jansport in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 01-30-2016, 03:09 PM
  3. [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
  4. 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
  5. [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
  6. [SOLVED] Dependent comboboxes
    By ObiWanBaloney in forum Excel General
    Replies: 6
    Last Post: 06-04-2012, 08:40 PM
  7. dependent comboboxes
    By humboldtguy in forum Excel Programming / VBA / Macros
    Replies: 15
    Last Post: 05-14-2010, 10:40 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