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

 

[Andrzejek]

I wouldn't use Control as a name of the object, it is a reserved word.

Try something like:

Private Sub Button1_Click()
    ClearListBox([blue]listbox1[/blue])
End Sub

Private Sub ClearListBox([blue]Ctrl As ListBox[/blue])
Dim i As Long

For i = 0 To Ctrl.ListCount - 1
    Ctrl.Selected(i) = False
Next

End Sub

Code not tested.

Have fun.

---- Andy

 

[Gruuuu]

Try the Value property

 

[GJP55]

Thanks for looking into it.
With a slight change it worked ! - Thanks

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