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
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

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.
 

Forum statistics

Threads
1,141,913
Messages
5,709,298
Members
421,625
Latest member
renrut_5

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
Top