Oddity When Trying To Remove Objects

A

Ark68

Well-known Member
Joined
Mar 23, 2004
Messages
4,570
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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 ...
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
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
Sort by date Sort by votes
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?
 
Upvote 0
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
 
Upvote 0
Yes, Norie is right about merging. Avoid if possible. Another good idea is to remove validation for the reset. Something like:
Code: 
With Range("A15:F25")
    .UnMerge
    .Validation.Delete
End With
 
Upvote 0
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?

Code: 
   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
 
Upvote 0
Do you have other objects on the page that do not respond to the address property maybe?

Amend code to add the following lines:
Code: 
    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.
 
Upvote 0
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
 
Upvote 0
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:

Code: 
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.
 
Upvote 0
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Solved
Removing Autofilter Without Resetting All Filters
Replies
3
Views
418
RoryA
RoryA
U
  • Question Question
Removing outlook signature in vba code
Replies
5
Views
3K
DanteAmor
DanteAmor
D
  • Question Question
Fastest way to filter a Listobject directly to an array
Replies
0
Views
668
dumitrudan608
D
WildBurrow
  • Question Question
Copy/Paste data to next empty row within a range - WITHOUT shifting subsequent rows up
Replies
0
Views
405
WildBurrow
WildBurrow
L
  • Question Question
Run-time error '1004' when Clearing Data Help
Replies
1
Views
558
lpo13
L

Forum statistics

Threads
1,223,924
Messages
6,175,415
Members
452,640
Latest member
steveridge

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top