How to specify a listbox when calling a sub

Skippy

Board Regular
Joined
Mar 3, 2002
Messages
194
I found this code for deleting duplicates in a ListBox. I'm calling it from another Sub and the name of the list is "lstLeft" on a user form called "frmOptions". How do I call the KillDupes and specify what the list box name is.
Rich (BB code):
Sub Kill_LBDupes(LB As ListBox)
    Dim idxLoop As Integer
    Dim idxCheck As Integer
    idxLoop = 0
    Do While idxLoop < LB.ListCount - 1 'Stop on 2nd from last item
        idxCheck = idxLoop + 1
        Do While idxCheck <= LB.ListCount - 1 'Stop on last item
            If LB.List(idxCheck) = LB.List(idxLoop) Then
                LB.RemoveItem idxCheck
            'We do not increment idxCheck here since the rest of the items are reindexed in the ListBox
            Else
                idxCheck = idxCheck + 1
            End If
        Loop
    idxLoop = idxLoop + 1
    Loop
End Sub

Sub CallFrom()
    KillDupes....????
End Sub
 

Some videos you may like

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

Skippy

Board Regular
Joined
Mar 3, 2002
Messages
194
Perhaps the question is not clear. When calling Kill_LBDupes, I need to pass the name of the list box that will be recognized as "LB" on this line:
Code:
Sub Kill_LBDupes(LB As ListBox)
.
I tried this, but it returns a Null value for lst.
Code:
Sub mySub ()
   Dim lst as Control

   Set lst = frmOptions.lstLeft
   Kill_LBDupes lst
End Sub
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,086
Office Version
  1. 365
Platform
  1. Windows
Skippy

As far as I can see that code would work, but only if the form frmOptions was actually displayed.

Is it?

I would actually think the best place to call KillDupes would be in the userform's initialize event.
 

Skippy

Board Regular
Joined
Mar 3, 2002
Messages
194
Yes, the form is displayed. I get a type mismatch error when I try to call Kill_LBDupes because lst is Null.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,086
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

What error message are you actually getting?

Are you sure it's because lst is 'Null'?

Is the name of the listbox lstLeft?

When are you actually calling this sub?

Try removing the type declaration in the KillDupes sub.
Code:
Sub Kill_LBDupes(LB) 
    Dim idxLoop As Integer 
    Dim idxCheck As Integer 
    idxLoop = 0 
    Do While idxLoop < LB.ListCount - 1 'Stop on 2nd from last item 
        idxCheck = idxLoop + 1 
        Do While idxCheck <= LB.ListCount - 1 'Stop on last item 
            If LB.List(idxCheck) = LB.List(idxLoop) Then 
                LB.RemoveItem idxCheck 
            'We do not increment idxCheck here since the rest of the items are reindexed in the ListBox 
            Else 
                idxCheck = idxCheck + 1 
            End If 
        Loop 
    idxLoop = idxLoop + 1 
    Loop 
End Sub
 

Skippy

Board Regular
Joined
Mar 3, 2002
Messages
194
Norie said:
Try removing the type declaration in the KillDupes sub.
That solved it. Before removing it, I debugged "Set lst = frmOptions.lstLeft" and lst returned "Null". Removing the type declaration allowed the correct ListBox name to be returned. Many thanks... :biggrin:
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,086
Office Version
  1. 365
Platform
  1. Windows
Skippy

Glad it's working.

I think the reason for the problem was because the KillDupes code was written for a different type of listbox, perhaps one from the Forms toolbar.

You might try declaring LB as MSForms.Listbox, if you want to.:)
Code:
Sub Kill_LBDupes(LB As MSForms.Listbox)
 

Skippy

Board Regular
Joined
Mar 3, 2002
Messages
194
Norie said:
You might try declaring LB as MSForms.Listbox, if you want to.:)
Code:
Sub Kill_LBDupes(LB As MSForms.Listbox)
That worked OK too. Thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,119,127
Messages
5,576,237
Members
412,709
Latest member
AD04
Top