tags:

views:

57

answers:

2

I am trying to delete a drop down from a spreadsheet using the following code:

Dim objShape As Shape
For Each objShape In ActiveSheet.Shapes
    objShape.Delete
Next

But objShape returns Application-Defined or Object-Defined Error:

Debug.Print objShape.FormControlType returns xlDropDown
Debug.Print objShape.Name                       returns "Drop Down 250"
Debug.Print objShape.TopLeftCell         returns Application-Defined or Object-Defined Error

ActiveSheet.Cells.Validation.Delete does not get rid of it, nor does using ShapeRange.

Any suggestions on how to eradicate this little bugger would be greatly appreciated.

A: 

Couple of ideas:

  • Is there some sheet protection?
  • Is the dropdown part of some validation (allowed values can be defined and are usually displayed as a dropdown box).
IronGoofy
Thought so myself, so before the code that attempts to delete the shape I have the following:`With ActiveSheet` `.Unprotect` `.Cells.Validation.Delete` `.Cells.ClearComments``End With`Thanks for the help.
Kuyenda
A: 

The drop down list was tied to another sheet in the workbook. After deleting the other sheet, the code correctly deleted the shape.

Kuyenda