Excel - VBA Removing Duplicates from Comboboxes

Question

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

Final Code

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

Matteo NNZ · Accepted Answer · 2015-02-12T16:03:29.950

1

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

"ComboBox" & X

will become, for example if x = 5,

ComboBox5

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

"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.

and finally recovering the "Xth" combobox like this:

With allCBoxes(X)

End With

edited Feb 12 '15 at 16:03

answered Feb 12 '15 at 15:54

Matteo NNZ

11,930
12
52
89

Your method solved the object issue as well, but now I get this error: "Could not get the List property. Invalid property array index." – Tyler Feb 12 '15 at 16:20
@Tyler On which line? – Matteo NNZ Feb 12 '15 at 16:21
As far as I can tell on the .RemoveItem j line in the nested if statement. – Tyler Feb 12 '15 at 16:29
It errors out right after I step from the line above it to that one. – Tyler Feb 12 '15 at 16:34
You only need to debug, the error you get is just a bad-list index one; step through the code in debug mode and you will fix it, for us is too hard without knowing what's going on in there. – Matteo NNZ Feb 12 '15 at 16:36
Thanks for all the help I got that sorted out now. Was just starting at an index that was too high and ending on one too low. Now the code works wonderfuly. – Tyler Feb 12 '15 at 16:46
@Tyler considering accepting one of the two answers then, so people with your same problem will know that here's the solution ;) – Matteo NNZ Feb 12 '15 at 16:51

score 1 · Answer 2 · answered Feb 12 '15 at 16:03

1

If you want to reference a control using its string name, use the Controls function.

Such as:

With Controls("Combobox" & X)

Does that resolve the problem?

answered Feb 12 '15 at 16:03

basodre

5,720
1
15
23

This solved the object issue, but now I get this error: "Could not get the List property. Invalid property array index." – Tyler Feb 12 '15 at 16:20
I'm going to add a new answer that can be used as an entirely different method of tackling the underlying problem: need a combobox without duplicates. See below. – basodre Feb 12 '15 at 16:50

score 0 · Answer 3 · answered Feb 12 '15 at 16:52

As mentioned in my comment above, here's a different approach towards solving the underlying problem: needing a combobox without duplicate values. This method uses a Dictionary object.

Let me know if you can adapt it to your needs, and if it works.

Private Sub UserForm_Initialize()
    Dim oDictionary As Object
    Dim strCellContent As String
    Dim rngComboValues As Range
    Dim rngCell As Range

    Set rngComboValues = Range("A1:A26")
    Set oDictionary = CreateObject("Scripting.Dictionary")

    For Each rngCell In rngComboValues
        strCellContent = rngCell.Value

        If Not oDictionary.exists(strCellContent) Then
            oDictionary.Add strCellContent, 0
        End If
    Next rngCell

    For Each itm In oDictionary.keys
        Me.ComboBox1.AddItem itm
    Next itm

    Set oDictionary = Nothing
End Sub

Excel - VBA Removing Duplicates from Comboboxes

3 Answers3

Linked