CODEWITHSUNDEEP
excelvba
Jason
Jason

Reputation: 7682

Deselect Excel list box form control with macro

I have a simple series of list boxes (form controls):

column of list boxes

And I am attempting to clear all list box selections on click of the "Reset" button, however I'm getting a Runtime error: '424' Object required. What's the proper way to reference the current sheet object (I'm assuming this is what the macro is looking for)?

Sub clearListBoxSelectionsF1()
    ListBox6.ListIndex = -1
    ListBox9.ListIndex = -1
    ListBox11.ListIndex = -1
    ListBox12.ListIndex = -1
    ListBox14.ListIndex = -1
    ListBox16.ListIndex = -1
    ListBox17.ListIndex = -1
    ListBox18.ListIndex = -1
    ListBox20.ListIndex = -1
    ListBox21.ListIndex = -1
    ListBox22.ListIndex = -1
    ListBox23.ListIndex = -1
    ListBox25.ListIndex = -1
    ListBox27.ListIndex = -1
    ListBox28.ListIndex = -1
    ListBox29.ListIndex = -1
    ListBox31.ListIndex = -1
    ListBox33.ListIndex = -1
End Sub

Upvotes: 0

Views: 65

Answers (1)

Pᴇʜ
Pᴇʜ

Reputation: 57683

If the ListBoxes are FormControls (which I recommend because staying away from ActiveX is always a good idea) the following should work:

Worksheets("Sheet1").Shapes("List Box 1").OLEFormat.Object.ListIndex = 0

So if you have them numbered continously (which I recommend) you can use a loop:

Dim ListBoxIndex As Long
For ListBoxIndex = 1 To 10
    Worksheets("Sheet1").Shapes("List Box " & ListBoxIndex).OLEFormat.Object.ListIndex = 0
Next ListBoxIndex

If they are not numbered continously make sure you rename them properly.

Alternatively if you want to reset all ListBoxes in a worksheet you can use something like:

Dim Shp As Shape
For Each Shp In Worksheets("Sheet1").Shapes
    If TypeName(Shp.OLEFormat.Object) = "ListBox" Then
        Shp.OLEFormat.Object.ListIndex = 0
    End If
Next Shp

Upvotes: 1

Related Questions