combo box

[davidson]

Hi all,

I want to create a combo box and I have entered the list of values by fillinmg in the range in ListFillRange, say D1:D5. Howebver, among the values in D1 to D5, there are some duplicate. Can we have a combox box without any duplicate value?

For example, if the values of D1, D2, D3, D4, D5 are a, b, c, b, a

I would like to have a combo box showing only 3 values, namely a, b, c. But now I have the value a, b, c, b, a.

Can anyone help? Thx

 

[agihcam]

welcome to the board.
try;

Dim a, e, dic As Object
With Sheets("sheet1") '<- change to suite
    a = .Range("a1", .Range("a" & Rows.Count).End(xlUp)).Value '<- change to suite
End With
If Not IsArray(a) Then Exit Sub
Set dic = CreateObject("Scripting.Dictionary")
dic.comparemode = vbTextCompare
For Each e In a
    If Not IsEmpty(e) Then
        If Not dic.exists(e) Then
            dic.Add e, Nothing
        End If
    End If
Next
Erase a
If dic.Count > 1 Then Me.ComboBox1.List = dic.keys
Set dic = Nothing

 

[davidson]

welcome to the board.
try;

Dim a, e, dic As Object
With Sheets("sheet1") '<- change to suite
    a = .Range("a1", .Range("a" & Rows.Count).End(xlUp)).Value '<- change to suite
End With
If Not IsArray(a) Then Exit Sub
Set dic = CreateObject("Scripting.Dictionary")
dic.comparemode = vbTextCompare
For Each e In a
    If Not IsEmpty(e) Then
        If Not dic.exists(e) Then
            dic.Add e, Nothing
        End If
    End If
Next
Erase a
If dic.Count > 1 Then Me.ComboBox1.List = dic.keys
Set dic = Nothing

Can you give me an example on how to change this line?

a = .Range("a1", .Range("a" & Rows.Count).End(xlUp)).Value '<- change to suite

Thanks a lot.

 

[davidson]

welcome to the board.
try;

Dim a, e, dic As Object
With Sheets("sheet1") '<- change to suite
    a = .Range("a1", .Range("a" & Rows.Count).End(xlUp)).Value '<- change to suite
End With
If Not IsArray(a) Then Exit Sub
Set dic = CreateObject("Scripting.Dictionary")
dic.comparemode = vbTextCompare
For Each e In a
    If Not IsEmpty(e) Then
        If Not dic.exists(e) Then
            dic.Add e, Nothing
        End If
    End If
Next
Erase a
If dic.Count > 1 Then Me.ComboBox1.List = dic.keys
Set dic = Nothing

Can you give me an example on how to change this line?

a = .Range("a1", .Range("a" & Rows.Count).End(xlUp)).Value '<- change to suite

Thanks a lot.

 

[agihcam]

Can you give me an example on how to change this line?

a = .Range("a1", .Range("a" & Rows.Count).End(xlUp)).Value '<- change to suite

this line will capture the data from A1:A& last row

if your data was in columnC for example then simply change "a1" to "c1" and .range("a" & rows.count...... to .range("c" & rows.count.....

EDIT: This code is from Jindon, so the credit goes to him. forgot to mentioned this in the first place (sorry).

 

[davidson]

Thanks. I have tried this by adding your code in the Private Sub ComboBox1_Change().

However, there is an error on this part Me.ComboBox1.List = dic.keys
when I change the comboBox.

May I know how to change it? Thanks

 

[jindon]

davidson,

Can I see your code?

 

[davidson]

Thanks Jindon for your help.

I have created a combBox1 and then add the following code.

Private dic As Object

Private Sub ComboBox1_Change()
Dim A, e, dic As Object
With Sheets("custom size")
A = .Range("a1", .Range("a" & Rows.Count).End(xlUp)).Value
End With
If Not IsArray(A) Then Exit Sub
Set dic = CreateObject("Scripting.Dictionary")
dic.CompareMOde = vbTextCompare
For Each e In A
If Not IsEmpty(e) Then
If Not dic.exists(e) Then
dic.Add e, Nothing
End If
End If
Next
Erase A
If dic.Count > 1 Then Me.ComboBox1.List = dic.keys <-ERROR in this line
Set dic = Nothing

End Sub

Thanks a lot.

 

[jindon]

Looks like you only have one combobox...
delete "Private dic As Object"
and try

Private Sub ComboBox1_Change()
Dim A, e, dic As Object
With Sheets("custom size")
    A = .Range("a1", .Range("a" & Rows.Count).End(xlUp)).Value
End With
If Not IsArray(A) Then Exit Sub
Set dic = CreateObject("Scripting.Dictionary")
dic.CompareMode = vbTextCompare
For Each e In A
    If Not IsEmpty(e) Then
        If Not dic.exists(e) Then
            dic.Add e, Nothing
        End If
    End If
Next
Erase A
If dic.Count > 0 Then Me.ComboBox1.List = dic.keys
Set dic = Nothing
End Sub

 

[davidson]

I still have the error in the line Me.ComboBox1.List = dic.keys
The error is 70 No authorization.