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
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
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
 
Upvote 0
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.
 
Upvote 0
Yes, the form is displayed. I get a type mismatch error when I try to call Kill_LBDupes because lst is Null.
 
Upvote 0
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
 
Upvote 0
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:
 
Upvote 0
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)
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,212,099
Messages
6,105,955
Members
447,986
Latest member
dicklim39

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top