Combobox List does not include duplicates & other
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: Combobox List does not include duplicates & other

  1. #1
    Board Regular MFish's Avatar
    Join Date
    May 2019
    Location
    California
    Posts
    70
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Question Combobox List does not include duplicates & other

    Hi, is it possible for the combobox.list to not show duplicates? Here's the code I have...

    Code:
    Private Sub UserForm_Initialize()
    Application.ScreenUpdating = False
    
    With Worksheets("Input new run")
       Me.cmbDelLH.List = .Range("B7", .Range("B7").End(xlDown)).Value
       Me.cmbLegDel.List = .Range("c7", .Range("C7").End(xlDown)).Value
       
    End With
    
    End Sub
    In column "B" I will have a lot of duplicates. Is there a way for the list to only be the "What's new" and not duplicated items? Example:

    Column B Column C
    A 1
    A 2
    B 1
    B 2
    B 3

    Basically in the first combobox it will only show two items to choose from... A and B.

    Now, is it possible to only show the list in combobox2 off of what was chosen in combobox1? In this case if I were to select selection "A", in combobox1, then in combobox2 it will only show the values of "1, 2" and leaving out 3 because the first combobox is selected on A, respective to the data above, offset(0,1)?

  2. #2
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    5,732
    Post Thanks / Like
    Mentioned
    65 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Combobox List does not include duplicates & other

    Try this

    Code:
    Private Sub ComboBox1_Change()
        ComboBox2.Clear
        If ComboBox1.ListIndex > -1 Then
            For i = 1 To Range("A" & Rows.Count).End(xlUp).Row
                If Cells(i, "A").Value = ComboBox1 Then
                    ComboBox2.AddItem Cells(i, "B")
                End If
            Next
        End If
    End Sub
    
    
    Private Sub UserForm_Activate()
        For i = 1 To Range("A" & Rows.Count).End(xlUp).Row
            existe = False
            For j = 0 To ComboBox1.ListCount - 1
                If Cells(i, "A").Value = ComboBox1.List(j) Then
                    existe = True
                    Exit For
                End If
            Next
            If existe = False Then
                ComboBox1.AddItem Cells(i, "A").Value
            End If
        Next
    End Sub
    Regards Dante Amor

  3. #3
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    26,976
    Post Thanks / Like
    Mentioned
    459 Post(s)
    Tagged
    45 Thread(s)

    Default Re: Combobox List does not include duplicates & other

    How about
    Code:
    Dim UfDic As Object
    
    Private Sub cmbDelLH_Change()
       Me.cmbLegDel.Clear
       Me.cmbLegDel.list = UfDic(Me.cmbDelLH.Value).Keys
    End Sub
    
    Private Sub UserForm_Initialize()
       Dim Cl As Range
       
       Set UfDic = CreateObject("scripting.dictionary")
       With Sheets("Input new run")
          For Each Cl In .Range("B7", .Range("B" & Rows.Count).End(xlUp))
             If Not UfDic.Exists(Cl.Value) Then UfDic.Add Cl.Value, CreateObject("scripting.dictionary")
             UfDic(Cl.Value)(Cl.Offset(, 1).Value) = Empty
          Next Cl
       End With
       Me.cmbDelLH.list = UfDic.Keys
    End Sub
    The line in red must go at the vary top of the module, before any code
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  4. #4
    Board Regular MFish's Avatar
    Join Date
    May 2019
    Location
    California
    Posts
    70
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Combobox List does not include duplicates & other

    Hi DanteAmor,

    I have plugged the codes in and it automatically came back with variable not defined for 'i'. I just but dim i as integer. Once I changed that and try to run the code it is asking what is "existe".

  5. #5
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    5,732
    Post Thanks / Like
    Mentioned
    65 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Combobox List does not include duplicates & other

    Code:
    Private Sub ComboBox1_Change()
     
         Dim i as long
    
    
        ComboBox2.Clear
        If ComboBox1.ListIndex > -1 Then
            For i = 1 To Range("A" & Rows.Count).End(xlUp).Row
                If Cells(i, "A").Value = ComboBox1 Then
                    ComboBox2.AddItem Cells(i, "B")
                End If
            Next
        End If
    End Sub
    
    
    
    
    Private Sub UserForm_Activate()
    
        Dim i As Long, existe As Boolean
    
    
        For i = 1 To Range("A" & Rows.Count).End(xlUp).Row
            existe = False
            For j = 0 To ComboBox1.ListCount - 1
                If Cells(i, "A").Value = ComboBox1.List(j) Then
                    existe = True
                    Exit For
                End If
            Next
            If existe = False Then
                ComboBox1.AddItem Cells(i, "A").Value
            End If
        Next
    End Sub
    Regards Dante Amor

  6. #6
    Board Regular MFish's Avatar
    Join Date
    May 2019
    Location
    California
    Posts
    70
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Combobox List does not include duplicates & other

    Quote Originally Posted by Fluff View Post
    How about
    Code:
    Dim UfDic As Object
    
    Private Sub cmbDelLH_Change()
       Me.cmbLegDel.Clear
       Me.cmbLegDel.list = UfDic(Me.cmbDelLH.Value).Keys
    End Sub
    
    Private Sub UserForm_Initialize()
       Dim Cl As Range
       
       Set UfDic = CreateObject("scripting.dictionary")
       With Sheets("Input new run")
          For Each Cl In .Range("B7", .Range("B" & Rows.Count).End(xlUp))
             If Not UfDic.Exists(Cl.Value) Then UfDic.Add Cl.Value, CreateObject("scripting.dictionary")
             UfDic(Cl.Value)(Cl.Offset(, 1).Value) = Empty
          Next Cl
       End With
       Me.cmbDelLH.list = UfDic.Keys
    End Sub
    The line in red must go at the vary top of the module, before any code
    Hey Fluff,

    So I input the code in and the first combobox1, named cmbDelLh will have the correct information when the drop down is selected, yet once I try to hit a selection it comes back as a runtime error of: 91 and highlights...

    Code:
    Me.cmbLegDel.list = UfDic(Me.cmbDelLH.Value).Keys

  7. #7
    Board Regular MFish's Avatar
    Join Date
    May 2019
    Location
    California
    Posts
    70
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Combobox List does not include duplicates & other

    Quote Originally Posted by DanteAmor View Post
    Code:
    Private Sub ComboBox1_Change()
     
         Dim i as long
    
    
        ComboBox2.Clear
        If ComboBox1.ListIndex > -1 Then
            For i = 1 To Range("A" & Rows.Count).End(xlUp).Row
                If Cells(i, "A").Value = ComboBox1 Then
                    ComboBox2.AddItem Cells(i, "B")
                End If
            Next
        End If
    End Sub
    
    
    
    
    Private Sub UserForm_Activate()
    
        Dim i As Long, existe As Boolean
    
    
        For i = 1 To Range("A" & Rows.Count).End(xlUp).Row
            existe = False
            For j = 0 To ComboBox1.ListCount - 1
                If Cells(i, "A").Value = ComboBox1.List(j) Then
                    existe = True
                    Exit For
                End If
            Next
            If existe = False Then
                ComboBox1.AddItem Cells(i, "A").Value
            End If
        Next
    End Sub
    This works good! Only issue it now how is my data of drop down list doesn't start until B7 and downward... So the blank space above AND the header at B6 is showing in the first combobox1, cmbDelLh. How do I subtract that and show only from B7 and downward?

  8. #8
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    5,732
    Post Thanks / Like
    Mentioned
    65 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Combobox List does not include duplicates & other

    Quote Originally Posted by MFish View Post
    This works good! Only issue it now how is my data of drop down list doesn't start until B7 and downward... So the blank space above AND the header at B6 is showing in the first combobox1, cmbDelLh. How do I subtract that and show only from B7 and downward?

    Code:
    Private Sub ComboBox1_Change()
         Dim i as long
        ComboBox2.Clear
        If ComboBox1.ListIndex > -1 Then
            For i = 7 To Range("A" & Rows.Count).End(xlUp).Row
                If Cells(i, "A").Value = ComboBox1 Then
                    ComboBox2.AddItem Cells(i, "B")
                End If
            Next
        End If
    End Sub
    
    
    Private Sub UserForm_Activate()
        Dim i As Long, existe As Boolean
        For i = 7 To Range("A" & Rows.Count).End(xlUp).Row
            existe = False
            For j = 0 To ComboBox1.ListCount - 1
                If Cells(i, "A").Value = ComboBox1.List(j) Then
                    existe = True
                    Exit For
                End If
            Next
            If existe = False Then
                ComboBox1.AddItem Cells(i, "A").Value
            End If
        Next
    End Sub
    It is important that future questions indicate exactly what your data is, so we can give you more complete solutions.
    Regards Dante Amor

  9. #9
    Board Regular MFish's Avatar
    Join Date
    May 2019
    Location
    California
    Posts
    70
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Combobox List does not include duplicates & other

    DanteAmor,

    You are awesome!!! Thank you. And will do with the future questions.

  10. #10
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    5,732
    Post Thanks / Like
    Mentioned
    65 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Combobox List does not include duplicates & other

    I'm glad to help you. Thanks for the feedback.
    Regards Dante Amor

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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