• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 442
  • Last Modified:

How to use VBA to remove values from a Listbox

I am trying to remove all values from a Listbox in a Worksheet (not a Userform).
I can use this code to de-select all values, but I haven't been able to find a way to actually remove all items.  I get a "method not supported by this object" if I try to use .Clear or RowSource = ""


Sub ListClear()

    Dim ws As Worksheet
    Dim LB As MSForms.ListBox
    Dim lCount As Long
   
    Set ws = ActiveSheet
    Set LB = ws.OLEObjects("ListBox2").Object
   
    With LB
        For lCount = 0 To .ListCount - 1
           .Items(lCount) = False
        Next
    End With
0
Ed_CLP
Asked:
Ed_CLP
  • 2
1 Solution
 
TinTombStoneCommented:
This worked for me

the AddItems are just to get something into the box
Sub ListClear()
Dim ws As Worksheet
    Dim LB As MSForms.ListBox
    Dim lCount As Long
    
    Set ws = ActiveSheet
    Set LB = ws.OLEObjects("ListBox2").Object
    
    LB.AddItem "Item 1"
    LB.AddItem "Item 2"
    LB.AddItem "Item 3"
    LB.AddItem "Item 4"
    LB.AddItem "Item 5"
   
    LB.Clear
 

End Sub

Open in new window

0
 
TinTombStoneCommented:
Or using your method


Sub ListClear()
Dim ws As Worksheet
    Dim LB As MSForms.ListBox
    Dim lCount As Long
    
    Set ws = ActiveSheet
    Set LB = ws.OLEObjects("ListBox2").Object
    
    LB.AddItem "Item 1"
    LB.AddItem "Item 2"
    LB.AddItem "Item 3"
    LB.AddItem "Item 4"
    LB.AddItem "Item 5"
   
    With LB
        For lCount = .ListCount - 1 To 0 Step -1
           LB.RemoveItem lCount
        Next
    End With

End Sub

Open in new window

0
 
Ed_CLPAuthor Commented:
I found the problem - The ListFillrange property is set to use a named range.  This apparently prevents you from changing any of the list values.  If I disable this property, your code works fine.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now