Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Apr 2015
    Posts
    193
    Thanks
    89
    Thanked 9 Times in 9 Posts

    Working with Two Combo Boxes

    Hi Experts,

    I am working with two combo boxes on a userform. So far, my efforts at having them work together consistently haven't happened. I tried to add an attachment but for some reason was unable to.

    Thanks in advance for your help,

    Excelnewbie


    Private Sub Commandbutton1_click()

    Lastrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row 'combo box doesn't work without this code

    With CommandButton1

    Select Case col
    Case 1

    col = 2

    Case 2

    col = 1

    If Not ComboBox1.Value = "*" Then
    Do Until ActiveSheet.Cells(Row, 2) = ComboBox1.Value Or Row > Lastrow


    Row = Row + 1

    Loop

    End If

    If Not ComboBox2.Value = "*" Then
    Do Until ActiveSheet.Cells(Row, 3) = ComboBox2.Value Or Row > Lastrow

    Row = Row + 1

    Loop

    End If


    End Select

    ActiveSheet.Cells(Row, col).Select
    TextBox1 = ActiveSheet.Cells(Row, col)
    If col = 2 Then Row = Row + 1

    'RB Added////////////////////////////
    If TextBox1 = "" Then
    MsgBox "You've Finished. Click to Continue or click Close to Finish"
    '///////////////////////////////////
    'This code refreshes after clicking the Message Box
    Unload Me
    UserForm1.Show
    'ActiveWorkbook.Worksheets("Sheet1").Range("A2").A ctivate
    '//////////////////////////////////
    End If
    End With

    End Sub

  2. #2
    2 Star Lounger
    Join Date
    Apr 2015
    Posts
    193
    Thanks
    89
    Thanked 9 Times in 9 Posts
    I found a way to upload a file.
    Attached Files Attached Files

  3. #3
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    3,076
    Thanks
    169
    Thanked 814 Times in 743 Posts
    Excel,

    Here is one method to get two comboboxes to work together

    Code:
    Private Sub ComboBox1_Change()
    '----------------------------------
    'DECLARE AND SET VARIABLES
    Dim dnary As Object
    Set dnary = CreateObject("Scripting.Dictionary")
    '----------------------------------
    'FIND COMBOBOX1 VALUE AND GET COMBOBOX2 VALUES
    ComboBox2.Clear
    LastRow = ActiveSheet.Cells(Rows.Count, 2).End(xlUp).Row
    Select Case ComboBox1.Value
        Case "*"
            For I = 2 To LastRow
    '----------------------------------
    'IF UNIQUE VALUE (NOT DUP)
                If Not dnary.Exists(Cells(I, 3).Value) Then
                    ComboBox2.AddItem Cells(I, 3)
                    dnary.Add Cells(I, 3).Value, 1
                End If
            Next I
        Case Else
            For I = 2 To 7
    '----------------------------------
    'IF UNIQUE VALUE (NOT DUP)
                If Not dnary.Exists(Cells(I, 3).Value) And Cells(I, 2) = ComboBox1.Value Then
                    ComboBox2.AddItem Cells(I, 3).Value
                    dnary.Add Cells(I, 3).Value, 1
                End If
            Next I
    End Select
    '----------------------------------
    'CLEANUP
    Set dnary = Nothing
    End Sub
    
    Private Sub Userform_Activate()
    With ComboBox1
        .AddItem "*"
        .AddItem "A"
        .AddItem "B"
        .AddItem "C"
    End With
    End Sub
    Based on selection in combobox1, combobox2 will populate with unique matching values.

    Note: I removed all other coding so to focus on the combo box syncing.

    HTH,
    Maud

  4. The Following User Says Thank You to Maudibe For This Useful Post:

    Excelnewbie (2017-09-13)

  5. #4
    2 Star Lounger
    Join Date
    Apr 2015
    Posts
    193
    Thanks
    89
    Thanked 9 Times in 9 Posts
    Thanks a million for helping.

    I worked on this for hours and what I came up with is close to working but doesn't work in every case. I attached a file for you to see.

    Thanks again for all you do.
    Attached Files Attached Files
    Last edited by Excelnewbie; 2017-09-14 at 08:25.

  6. #5
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    3,076
    Thanks
    169
    Thanked 814 Times in 743 Posts
    Excel,

    I think this accomplishes what you are looking to achieve. The user selects a rating from Combox1 (column B). Combobox2 populates with all subjects from column C. The user selects a subject from combobox2. Then the user clicks the next button, the movies from column A whose rating and subject matches the criteria from the comboboxes are listed in textbox1 on the form.

    HTH,
    Maud

    Excel2.png

    WS-Combo Boxes_Rev2.xlsm

  7. The Following User Says Thank You to Maudibe For This Useful Post:

    Excelnewbie (2017-09-16)

  8. #6
    2 Star Lounger
    Join Date
    Apr 2015
    Posts
    193
    Thanks
    89
    Thanked 9 Times in 9 Posts
    Maud,

    Thank you for taking your time to help. You're the best. I will study the code you provided.

  9. The Following User Says Thank You to Excelnewbie For This Useful Post:

    Maudibe (2017-09-16)

  10. #7
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    3,076
    Thanks
    169
    Thanked 814 Times in 743 Posts
    Thanks Excel, your posts are often very challenging. Glad to help

  11. #8
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,366
    Thanks
    4
    Thanked 235 Times in 216 Posts
    Purely for information, if you have a lot of data, it would be faster to load the ranges into an array, dump them into the dictionary as appropriate (no need to test for prior existence) and then load all the keys into the combobox in one shot at the end, like this:

    Code:
            Select Case ComboBox1.Value
                Case "*"
                    data = Range(Cells(2, 3), Cells(lastrow, 3)).Value
                    ' this will add a new item if necessary or overwrite an existing one if not
                    For i = 1 To UBound(data)
                        dnary(data(i, 1)) = Empty
                    Next i
                    ' can then assign all the dictionary keys in one hit.
                    If dnary.Count > 0 Then ComboBox2.List = dnary.keys
                Case Else
                    data = Range(Cells(2, 2), Cells(lastrow, 3)).Value
                    For i = 1 To UBound(data)
                        If data(i, 1) = ComboBox1.Value Then dnary(data(i, 2)) = Empty
                    Next i
                    If dnary.Count > 0 Then ComboBox2.List = dnary.keys
            End Select
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •