How to Clear Listbox Form Control

[Mordred]

Hey all,

I have the following code

Please Login or Register  to view this content.

You'll notice that .Clear is commented out. I would like to be able to clear this listbox, sort of like a refresh but I keep getting an error

Object doesn't support property or method

How can I perform a clear.

Also, if snb is watching, how do you not use .AddItem in a situation like this where the list box is a form control / shape.

[tigeravatar]

Mordred,

For an ActiveX control, its best to treat it as an OLEObject. There is no .Clear (that I know of) for an ActiveX listbox, but the list is from the ListFillRange and you can set that to vbnullstring. Also, if you have the list on a worksheet somewhere, you can set up a named range and then set the listfillrange to that named range. See attached workbook for an example. There's a module with the following sub:

Please Login or Register  to view this content.

Hope that helps,
~tigeravatar

[Mordred]

Hi tigeravatar, thanks for your response. I tried your procedure but it throws an error when setting lb. I think this is because it is not an ActiveX control but a form control. In your workbook that you made up (thanks for that) you are using an ActiveX list box but I have had problems with them resizing on me so that is why I don't use them on a sheet. Can your code be tweaked to do the same with a form control list box?

[tigeravatar]

Mordred,

Updated code and attachment. Form controls are not OLEObjects and are treated as their respective control types. You'll notice that lb is dimmed as a listbox instead of an oleobject this time. Other than that, they're basically identical:

Please Login or Register  to view this content.

Hope that helps,
~tigeravatar

[tigeravatar]

Got ahead of myself, hehe. The form control listbox does have a .List property which you can use instead of the .ListFillRange. It'll do the same thing, but saves some typing:

Please Login or Register  to view this content.

[Mordred]

Hmm, no clearing seems to happen although I can step through the code without error.

[Mordred]

You are awesome tigeravatar and thank you. Your last post did exactly what is needed.

[tigeravatar]

You're very welcome

[Mordred]

A quick question tigeravatar, how does the following know where the Mylist is

Please Login or Register  to view this content.

. I looked to see if you named a range but you did not. Are you able to elaborate on this?

[Colin Legg]

FYI, forms listboxes have a RemoveAllItems method.

[tigeravatar]

@Mordred: There is a named range in the workbook I posted named MyList. It is defined with the following formula:
=INDIRECT("Chart_Tool!$A$2:$A$"&COUNTA(Chart_Tool!$A:$A))

@Colin: I was unaware of that, thanks for the tip

[Colin Legg]

You might be interested to know how I knew that? There's no mystery to these controls if you know where to look.

Forms controls are hidden members so you can't see them in the object browser unless you right click > show hidden members. You can then locate the Dropdown/ListBox class (which are Forms combobox/listbox) in the Excel object library and see all of the available members. Attached screenshot shows all of this.

[snb]

You can populate a dropdown wusing .List
You can clear a dropdown using RemoveAllitems or .List=Array("")

Please Login or Register  to view this content.