Find the Name of a created control and delete it.

[dtennis]

VBA on an Excel 2000 Worksheet

Using the following code I create a list box.

Set lstSubs = .Shapes.AddFormControl(xlListBox, 400, 700, 200, 200)

The next time someone opens this worksheet I want to make sure that this listbox is deleted so that when this code runs again it will not just keep putting one listbox on top of another. I am having problems finding the name of this listbox to delete the control.

Thanks

 

[VBAjedi]

I'd suggest naming the control right after you create it:

Set lstSubs = .Shapes.AddFormControl(xlListBox, 400, 700, 200, 200)
lstSubs.Name = "MyName"

Then, use that name to delete the control in the Workbook_Open or the Workbook_BeforeClose events:

On error resume next ' in case it doesn't exist
Shapes("MyName&quot.Delete
On error goto 0


Let me know if that helps!

VBAjedi