combo box

davidson

New Member
Joined
Oct 19, 2006
Messages
32
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
 

Some videos you may like

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

agihcam

Well-known Member
Joined
Jan 16, 2006
Messages
1,624
welcome to the board.
try;
Code:
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

New Member
Joined
Oct 19, 2006
Messages
32
welcome to the board.
try;
Code:
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

New Member
Joined
Oct 19, 2006
Messages
32
welcome to the board.
try;
Code:
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

Well-known Member
Joined
Jan 16, 2006
Messages
1,624

ADVERTISEMENT

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

New Member
Joined
Oct 19, 2006
Messages
32
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

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995

ADVERTISEMENT

davidson,

Can I see your code?
 

davidson

New Member
Joined
Oct 19, 2006
Messages
32
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

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
Looks like you only have one combobox...
delete "Private dic As Object"
and try
Code:
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

New Member
Joined
Oct 19, 2006
Messages
32
I still have the error in the line Me.ComboBox1.List = dic.keys
The error is 70 No authorization.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,051
Messages
5,545,725
Members
410,702
Latest member
clizama18
Top