Excel VBA 将项目添加到组合框而没有重复项目
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/28609687/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel VBA adding items to combo box without repeated items
提问by javad
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
I want the combobox result to be john, marry, lisaand frank(four unique items instead of eight items).
我想组合框的结果是john，marry，lisa和frank（而不是八个项目四个独特的项目）。
My code is:
我的代码是：
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
回答by user3561813
An alternative approach to adding unique items is to use a Dictionaryobject.
添加唯一项的另一种方法是使用Dictionary对象。
See below:
见下文：
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
回答by Davesexcel
Sub UsingCount()
Dim ws As Worksheet
Dim Rws As Long, Rng As Range, c As Range, y As Integer, x
Set ws = Sheets("Sheet1")
Sheets("Sheet3").ComboBox1.Clear
With ws
Rws = .Cells(Rows.Count, "A").End(xlUp).Row
For y = 1 To Rws
Set c = .Cells(y, 1)
Set Rng = .Range(.Cells(2, 1), .Cells(y, 1))
x = Application.WorksheetFunction.CountIf(Rng, c)
If x = 1 Then Sheets("Sheet3").ComboBox1.AddItem c
Next y
End With
End Sub
