pass an active x listbox name into a procedure in excel

[gjp55]

Hi,
I am trying to find a way to pass an active x listbox control into a procedure to deselect its selection. I have several listboxes that a user makes multi value selections in but would like to use the same function to deselect the items of a listbox by passing in the relevant listbox control name(or whatever is needed). This works fine if I want to resize a list box ,make it visible etc but when trying to read the selected items and then deselect them I get "Object doesn't support this property or method"

Is it not possible to pass in a listbox control and use this type of functionality ? I have other similar types of functionality I would like to do with the listboxes without having to repeat the same code over again for each listbox event.

Thanks
GJP55

Private Sub Button1_Click()

ClearListBox(listbox1.name)

End Sub


Private Sub ClearListBox(CtrlName As String)

Dim WS As Worksheet
Dim Control As OLEObject

Set WS = ThisWorkbook.Sheets("Filters")

Dim i As Long
For Each Control In WS.OLEObjects
If Control.Name = CtrlName Then
For i = 0 To Control.ListCount - 1
Control.Selected(i) = False
Next
End If


Next Control

End Sub

 

[RoryA]

It's easier if you pass the actual control:

Private Sub Button1_Click()
 
ClearListBox listbox1
 
End Sub
 
 
Private Sub ClearListBox(Ctrl As MSForms.Listbox)
 
Dim i as Long
For i = 0 To ctrl.ListCount - 1
ctrl.Selected(i) = False
Next i
 
End Sub

 

[gjp55]

Absolutley perfect , works flawlessly !
Thanks very much for responding so quickly - outstanding.

 

[gjp55]

Do you know how I could just let the code look at all listbox controls to perform the actions within the loop without needing to pass in a listbox as a parameter ? The code works so quickly that Im thinking it can call this to run for all listboxes anytime a single list box is updated.
Thanks

 

[RoryA]

If you want to do every one on the Filters sheet, then:

Private Sub ClearListBoxes()

   Dim WS As Worksheet
   Dim objOLE As OLEObject, LB As MSForms.ListBox
   Dim i As Long

   Set WS = ThisWorkbook.Sheets("Filters")

   For Each objOLE In WS.OLEObjects
      If TypeOf objOLE.Object Is MSForms.ListBox Then
         Set LB = objOLE.Object
         For i = 0 To LB.ListCount - 1
            LB.Selected(i) = False
         Next i
      End If
   Next objOLE
End Sub

 

[gjp55]

Again, works perfectly.
Many thanks for your help and prompt responses.
Truely outstanding !

 

[RoryA]

Glad to help.