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

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

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,343
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,343
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,343
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,621
Messages
5,832,736
Members
430,160
Latest member
a_majda

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
Top