Oddity When Trying To Remove Objects

[Ark68]

So, imagine this.

I have a workspace on my worksheet ... liken it to a user form. Its a range of say A15:F25.
Within this workarea are a series of cells that appear/disappear based on a users input.
There are also objects that I assign macros to to act as navigation type buttons. This workarea is used for many different activities throughout my application, and is altered in "blocks" depending on the activity. It is never really static.

When I need to reset this work area, I have found it easiest to just remove all the objects with this code ...

For Each PicObj In .Shapes
        TheLeft = PicObj.TopLeftCell.Address
        Set isect = Application.Intersect(.Range(TheLeft), .Range("A15:F25"))
   If Not isect Is Nothing Then PicObj.Delete

And copy the default work area from another worksheet overtop the existing workarea. This ensures that the workarea is reset.

However, I have run into a problem.
At one point, my workarea has a merged cell with a list validation.
When I copy over the default range, everything seems OK.
However, when I apply the code to remove the objects, I get a "Run Time Error 1004, Application-defined or Object-defined error." in my object removal routine. It stops at TheLeft = PicObj.TopLeftCell.Address.

To observe the work area reveals rements of the merged cell with the validation list. It is no longer an active cell but it has a border around it with the little drop down arrow next to it.

I don't quite understand what it going on. That cell should have been reset with the copy of the default range which is supposed to elimate these fields.

Can anyone suggest what I can consider as a cause of this?

Jenn

 

[Ark68]

Turns out that this issue has branched out into a much more debilitating problem. There are ghostly images of drop down being left somewhere on my worksheet, and and until I can find them, and get rid of them, they will continue to hinder the effectiveness of my application. The error handling has handled the annoying picobject thing, but this is far more serious.

See http://www.mrexcel.com/forum/showthread.php?t=463823 I realize this may be cross posting, but this message is relating more to DSCG's code and how I can use it to overcome both problems.

Considering DSCG's little code, I'd like to improve on it to find "whatever" drop down. Turns out that the image is volatile and never has the same name, so defining a name in his code fails. Any suggestions on how I can isolate the offending image, the source, and prevention of it reoccuring.

I use drop down validation only once in my application. They are a series of 4 cells called upon from each other in worksheet changes. These cells are copied to the worksheet from another source sheet, with the validation in tact. Once on the target sheet, the validation drop down arrows disappear although the validation still functions. If I rebuild the target sheet, eliminate the old one, everything works ... for a while ... the problem finds its way back to the sheet eventually. In my application, the drop-down validated cells are removed in a process of resetting the page to it's default stage (which excludes these cells). This cleansing is done with a copy/paste of a range of blank cells, formatted only with border and fill characteristics. A line of code to delete any validation in that same range is applied.

Any advice folks. This application, as many know, has been 2 years in the making, and I ami in the final straightaway.

Jenn