I'm having trouble removing some shapes from my worksheet

[EirikDaude]

I have a worksheet which doesn't seem to contain any shapes (see attached workbook).

However, when I run the macro named "test1", it indicates that there is one textbox and one button on the sheet.

When trying to remove these shapes, using the macro "test2", I get an error. I also get an error if I try to select them using "sh.select"

I am also having no luck spotting them anywhere on the worksheet.


Of course it's no huge issue that they are there, considering that it seems to be very hard to interact with them, but even so it kinda bugs me that there is stuff in my workbook which doesn't serve any purpose. So, do any of you guys have suggestions for how I can remove them / interact with them?

test.xlsm

[Aussiebear]

Ran the macros in Office for mac 2011 and Test 1 ran fine, test2 failed with 1004 error message which leads me to believe that there is no shapes to delete. Just as a second thought, the code should be constructed with a gate to exit the sub if no shapes are found, but that's my style I guess

[EirikDaude]

But test1 prints the name (and type) of two shapes to the immediate window, meaning there issomething there... For the record, it is the 1004 error I get when running test2 too, in Microsoft Excel 2010 (14.0.7109.5000) SP1 MSO (14.0.6129.5000).

[Aflatoon]

The only way I can find to get rid of them is to unzip the file and remove the relevant drawing parts and relationships.

[jonh]

They are hidden.

File
Options
Advanced
Display options for this workbook
For objects, show.
All

or

ActiveWorkbook.DisplayDrawingObjects = True

[EirikDaude]

Gah, I should have thought of that possibility!

Thanks a lot for your help! I really didn't look forward to figuring out how to unzip the file...

[Aussiebear]

Or for Mac's Excel/Preferences/View/For Objects/All Ok

[SamT]

FYI, in the future try deleting them in reverse order.

On Error Resume Next
For i = shapes.Count to 1 step - 1
Shapes(i).Delete
Next

[snb]

Or

Sub M_snb()
    Sheet1.Shapes.SelectAll
    Selection.Delete
End Sub

[Aflatoon]

Neither of which work until you have the objects visible (in which case the original code works too).

[SamT]

I thought that hidden Objects were still included in the Object Collection. Are Shape Objects different from others

[Aflatoon]

They are still part of the collection, you just can't do anything with them, including deleting them.

[EirikDaude]

Thanks again for your feedback. It is interesting seeing different approaches to the problem. Out of curiosity, using snb's method of SelectAll, is there any way to select all shapes of a particular type?

[snb]

Sub M_snb()
    Sheet1.Pictures.Select
    Sheet1.OLEObjects.Select
End Sub

[GTO]

Thanks again for your feedback. It is interesting seeing different approaches to the problem. Out of curiosity, using snb's method of SelectAll, is there any way to select all shapes of a particular type?

Presumably you mean after making the objects visible. Not that I'd be likely to run into the problem (stuff hidden under options), but interesting nevertheless :-) (or in my simple-minded terms, [BLEEP!], there's another way of goobering things up?!)

[EirikDaude]

Obviously I suspect that the problem was that there was a range of rows and/or columns which were hidden, in which the shapes were located. While updating the sheet, I deleted those ranges, but that didn't delete the shapes. Which created the problem described in the OP.

Hmm, while I appreciate the input snb, that's not really selecting the shapes by what type they are, but rather by identifying the objects by some other group they are a member of?

[snb]

Resulting in a subset of the shapes.selectall collection, which would also be the effect of a selection on the basis of 'type'.