Excel - VBA删除组合框中的重复 [英] Excel - VBA Removing Duplicates from Comboboxes

问题描述

我正在尝试创建一个子程序来删除组合框中的重复项。当我调用子程序时，我输入一个数字代替X。我得到一个错误，告诉我对象必需，当我到达子程序。我知道这意味着某些东西没有被正确的初始化，但是我不知道如何解决我的问题。任何帮助将不胜感激。谢谢。

I am trying to create a subroutine to delete duplicates out of comboboxes. I input a number in place of X when I called the subroutine. I keep getting an error that tells me "Object Required" when i get to the subroutine. I know that means that something is not being properly initialized, but I cannot figure out how to fix my issue. Any help would be greatly appreciated. Thank you.

Private Sub UserForm_Initialize()

'ComboBox Populate
Dim rngNext As Range
Dim myRange As Range
Dim C As Integer
With Sheets("KEY")
Set rngNext = .Range("B500").End(xlUp).Offset(1, 0)
End With
rngNext.Select
Set myRange = Range("B2", rngNext)

With ComboBox1
For Each rngNext In myRange

If rngNext <> "" Then .AddItem rngNext

Next rngNext
End With

Call RemoveDuplicates(1)
End sub

 Private Sub RemoveDuplicates(X)
'Remove Duplicates

Dim i As Long
Dim j As Long
With "ComboBox" & X
    For i = 0 To .ListCount + 1 'Getting object required error in this line
        For j = .ListCount To (i + 1) Step -1
            If .List(j) = .List(i) Then
                .RemoveItem j
            End If
        Next
    Next
End With
End Sub

最终代码

一切都可以很好的删除重复。

Everything works great for removing duplicates.

Public allCBoxes As Collection

Private Sub UserForm_Initialize()


Set allCBoxes = New Collection
allCBoxes.Add ComboBox1

'ComboBox Populate
Dim rngNext As Range
Dim myRange As Range
Dim C As Integer
With Sheets("KEY")
Set rngNext = .Range("B500").End(xlUp).Offset(1, 0)
End With
rngNext.Select
Set myRange = Range("B2", rngNext)

With ComboBox1
For Each rngNext In myRange
If rngNext <> "" Then .AddItem rngNext
Next rngNext
End With

Call RemoveDuplicates(1)
End sub

 Private Sub RemoveDuplicates(X)
'Remove Duplicates
Dim i As Long
Dim j As Long
With allCBoxes(X)
    For i = 0 To .ListCount + 1
        For j = .ListCount -1 To (i + 1) Step -1
            If .List(j) = .List(i) Then
                .RemoveItem j
            End If
        Next
    Next
End With
End Sub

推荐答案

您收到一个错误，因为您传递一个字符串而不是一个对象。
虽然直观地你可以认为：

You get an error because you're passing a string, not an object. Although intuitively you can think that:

"ComboBox" & X

将成为，例如，如果x = 5，

will become, for example if x = 5,

ComboBox5

你错了，因为你实际上建立一个字符串：

you're wrong because you're actually building a string:

"ComboBox5"

显然，如果您在String上调用ComboBox对象的方法，则会提示您输入Object Required。
你想做什么是不可能在VBA，你不能在运行时定义变量名称（即 ComboBox& X ，即使不是作为字符串，不会引用变量 ComboBox5 ）。为了达到你想要的目的，我建议创建一个公共收藏：

And, clearly, if you call a method of a ComboBox object on a String, you will be prompted of "Object Required". What you want to do is impossible in VBA, where you cannot define variable names at run-time (i.e. ComboBox & X, even if not "as string", will not reference the variable ComboBox5). To reach what you want, I suggest to create a public collection:

Dim allCBoxes As Collection

然后在主程序中填充它：

then to populate it on the main procedure:

Set allCBoxes = New Collection
allCBoxes.Add ComboBox1
allCboxes.Add ComboBox2
'etc.

，最后恢复Xth组合框：

and finally recovering the "Xth" combobox like this:

With allCBoxes(X)

End With

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