Thread: Removing all items from a list box

This is my attempt at removing all items from a worksheet listbox active X control that doesn't work. I'm probably not even along the right lines. This might not make any sense:

For i = 0 To ws.OLEObjects("ListBox1").Object.ListCount - 1

ws.OLEObjects("ListBox1").Object.RemoveItem ws.OLEObjects("ListBox1").Object.List(i)

Next i

ws.OLEObjects("ListBox1") is definitely the correct way of referencing the list box if that helps you identify what type of list box it is that I'm using.

Many thanks in advance for any help that can be offered,

Rowan

The following code which would be inside the sheet containing the listbox will add some items then the second macro will clear the listbox

Sub lbControl()
For k = 1 To 10
ListBox1.AddItem "Hello there number " & CStr(k)
Next
End Sub

Sub lbControlRm()
'to remove just one item from the listbox
ListBox1.RemoveItem (2) ' where 2 is the index number which starts at 0
'or to clear the entire listbox
ListBox1.Clear
End Sub

If you are doing this from a module which is what I assume you are doing you need to (as you have done) reference the listbox

Sub lbControl()
Set ws = Worksheets("Sheet1")
For k = 1 To 10
ws.ListBox1.AddItem "Hello there number " & CStr(k)
Next
End Sub

Sub lbControlRm()
Set ws = Worksheets("Sheet1")
'to remove just one item from the listbox
ws.ListBox1.RemoveItem (2)
'or to clear the entire listbox
ws.ListBox1.Clear
End Sub

Hope this helps.
Anthony
“Confidence never comes from having all the answers; it comes from being open to all the questions.”
PS: Remember to mark your questions as Solved once you are satisfied and rate the answer(s) questions.”