Combobox List does not include duplicates & other

[MFish]

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

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

 

[DanteAmor]

Try this

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

 

[Fluff]

How about

[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

 

[MFish]

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".

 

[DanteAmor]

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

 

[MFish]

How about

[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...

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

 

[MFish]

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?

 

[DanteAmor]

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?

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.

 

[MFish]

DanteAmor,

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

 

[DanteAmor]

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