Excel VBA将项目添加到组合框中，没有重复的项目 [英] Excel VBA adding items to combo box without repeated items

问题描述

我想在组合框中添加以下项目，但是如果某项目重复，则只能添加一个.

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

这篇关于Excel VBA将项目添加到组合框中，没有重复的项目的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！