PDA

View Full Version : [SOLVED] I'm having trouble removing some shapes from my worksheet



EirikDaude
12-10-2014, 03:16 AM
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?

12603

Aussiebear
12-10-2014, 04:20 AM
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
12-10-2014, 04:33 AM
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
12-10-2014, 04:47 AM
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
12-10-2014, 05:25 AM
They are hidden.

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

or

ActiveWorkbook.DisplayDrawingObjects = True

EirikDaude
12-10-2014, 06:44 AM
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
12-10-2014, 04:25 PM
Or for Mac's Excel/Preferences/View/For Objects/All Ok

SamT
12-10-2014, 05:51 PM
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
12-11-2014, 04:36 AM
Or

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

Aflatoon
12-11-2014, 04:48 AM
Neither of which work until you have the objects visible (in which case the original code works too). ;)

SamT
12-11-2014, 12:10 PM
I thought that hidden Objects were still included in the Object Collection. Are Shape Objects different from others

Aflatoon
12-11-2014, 01:56 PM
They are still part of the collection, you just can't do anything with them, including deleting them.

EirikDaude
12-12-2014, 03:18 AM
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
12-12-2014, 03:49 AM
Sub M_snb()
Sheet1.Pictures.Select
Sheet1.OLEObjects.Select
End Sub

GTO
12-12-2014, 05:34 AM
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
12-12-2014, 03:00 PM
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
12-13-2014, 09:39 AM
Resulting in a subset of the shapes.selectall collection, which would also be the effect of a selection on the basis of 'type'.