Combobox List does not include duplicates & other

MFish

Board Regular
Joined
May 9, 2019
Messages
76
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
[TABLE="width: 500"]
<tbody>[TR]
[TD]A
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]A
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]B
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]B
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]B
[/TD]
[TD]3
[/TD]
[/TR]
</tbody>[/TABLE]

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)?
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
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
 
Upvote 0
How about
Code:
[COLOR=#ff0000]Dim UfDic As Object[/COLOR]

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
 
Upvote 0
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".
 
Upvote 0
Code:
Private Sub ComboBox1_Change()
 
    [COLOR=#ff0000] Dim i as long[/COLOR]


    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()

[COLOR=#ff0000]    Dim i As Long, existe As Boolean[/COLOR]


    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
 
Upvote 0
How about
Code:
[COLOR=#ff0000]Dim UfDic As Object[/COLOR]

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
 
Upvote 0
Code:
Private Sub ComboBox1_Change()
 
    [COLOR=#ff0000] Dim i as long[/COLOR]


    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()

[COLOR=#ff0000]    Dim i As Long, existe As Boolean[/COLOR]


    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?
 
Upvote 0
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 = [B][COLOR=#ff0000]7[/COLOR][/B] 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 = [B][COLOR=#ff0000]7[/COLOR][/B] 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.
 
Upvote 0
DanteAmor,

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

Forum statistics

Threads
1,224,820
Messages
6,181,162
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top