Excel VBA将项目添加到组合框中，没有重复的项目
问题描述：
我想在组合框中添加以下项目，但是如果某项目重复，则只能添加一个.
I want to add below items to combobox but if there are duplicates of an item then only one should be added.
A
1 john
2 john
3 marry
4 marry
5 john
6 lisa
7 frank
8 marry
我希望组合框结果为john，marry，lisa和frank(四个唯一项，而不是八个项).
I want the combobox result to be john, marry, lisa and frank (four unique items instead of eight items).
我的代码是:
Private Sub Workbook_Open()
Application.EnableEvents = False
With Sheet2.ComboBox1
For Each Cell In Sheet1.Range("A1:A6348")
If Not ComboBox1.exists(Cell.Value) Then
.AddItem Cell.Value
End If
Next
End With
End Sub
答
添加唯一项的另一种方法是使用Dictionary对象.
An alternative approach to adding unique items is to use a Dictionary object.
参见下文:
Dim rngItems As Range
Dim oDictionary As Object
Set rngItems = Range("A1:A8")
Set oDictionary = CreateObject("Scripting.Dictionary")
With Sheet1.ComboBox21
For Each cel In rngItems
If oDictionary.exists(cel.Value) Then
'Do Nothing
Else
oDictionary.Add cel.Value, 0
.AddItem cel.Value
End If
Next cel
End With