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

 

[Norie]

Jenn

Merged cells?

 

[Andrew Poulsom]

Sounds like another reason to avoid merged cells when using VBA. Do you get the same problem if the data validation is in a single cell?

 

[Ark68]

Doh! Those crazy merged cells.
Regrettaby, they are an integral part of my application. (Visually impaired need big fields to enter data).
Is there a line of code I can add to my resetting that will unmerge any/all cells within the workarea range before I apply my object removal? I thought that woud have been accomplished with my copying of the default range to the workarea. The copied default range has no merged cells.

Jenn

 

[dscg]

Yes, Norie is right about merging. Avoid if possible. Another good idea is to remove validation for the reset. Something like:

With Range("A15:F25")
    .UnMerge
    .Validation.Delete
End With

 

[Ark68]

OK guys, I'm still failing to get rid of that error.
DSCG, I did as you had suggested, and included the unmerge and delete validation from the range after my default had been copied, and prior to object removal.
So ... ideally, this range should be empty, with all cells being individual with only basic shading formats and without values, except for any residual objects.

However, when it reaches this code, I get this error again.
Any additional thoughts?

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

 

[dscg]

Do you have other objects on the page that do not respond to the address property maybe?

Amend code to add the following lines:

    For Each picObj In .Shapes
        Debug.Print picObj.Name
        Debug.Print picObj.TopLeftCell.Address
        TheLeft = picObj.TopLeftCell.Address

Then in the VBA Editor - on the Menu at the top goto View -> Immediate Window
to make sure that window is displayed at the bottom. Then step through the routine with F8 key to see which object it's bombing out on.

 

[Ark68]

Hi DSCG ....

I have tried your suggestion ... and "Drop Down 840" displays in the immediate window. It stops with the error before it gets to the line identifying the cell.

Can I assume this is what is causing the error?

I don't know what Drop Down 840 refers to. I do use a validation (list) on one of my cells, but I use a line .validation.delete thinking that will take care of that.

What do I do now??

Jenn

 

[dscg]

Yes, I think you've found the problem. You have a Form Control Combo Box somewhere on your sheet. To identify it, perform the following:

Sub Marine()

ActiveSheet.Shapes("Drop Down 840").Visible = True
ActiveSheet.Shapes("Drop Down 840").Select

End Sub

Then you can decide if you want to keep it or not.

 

[Ark68]

I've determined these boxes will always be a problem ... they are remnents of my validation drop downs that my .validation.delete isn't removing. (It only leaves one of the three that are used in the range ... odd.) They are critical in my application, so as much as it bothers me, I've just ignored them. Out of sight, out of mind ??? LOL

So far they haven't caused any grief other than the grief of them clinging on.

Jenn