Refreshing shapes that cause excel to not save

[scottiex]

So I have problem that sometimes (but not often) occurs with a sheet I have created.
The sheet has a number of shapes in it that I use as buttons and it seems that sometimes this will cause excel to have the 'Errors were detected while saving" error. (I imagine that others may have encountered this issue).

The fix I have found is to delete those shapes and then to put them back in again. But that is going to be quite a pain to do manually with a large number of shapes to name and position exactly right.

So I want to be able to recreate the buttons on the sheet.

I have a template that I could copy the shapes or the shape settings from. So my tentative thought was that I could delete all the shapes and then for every shape I find in the template go to the main file and create something identical (I think these are all that matters : name, text, type, left top width and height and macro linked to it)?

Although it would also be good if it just recorded the shape settings deleted them and recreated them.

[AlphaFrog]

You could record a macro while creating the shapes. Your steps will be recorded in the macro. It's likely you'll have to further edit the macro after, but if you post the code here, someone would probably help clean it up for you.

[jolivanes]

Would you not be better off to find the cause of the error?

[Kevin#]

I agree with AlphaFrog.
Shapes are complicated. They have properties and various sub-attributes for colour fill etc. Trying to write a macro to capture all these properties and sub-attributes (so that shapes could be deleted and then re-created) could be very time consuming.
This link includes a good explanation

I would
- record a separate macro for each shape, including assigning a macro to it
- write "ReBuild" macro deleting all shapes on sheet before re-building them (by calling each recorded macro in turn)
- until the error is sorted auto-run the re-build before the file is closed & save each time

The recorded macros should do the job perfectly well - they capture everything required at the time. As AlphaFrog says, they could be cleaned up - but I would not personally bother - the extra lines are quite harmless.
Recording a separate macro for each shape (naming it appropriately) makes it easier to identify what to change later if you want to amend or remove one of the shapes..

To delete all the shapes in active sheet

Please Login or Register  to view this content.

Errors while saving
Having automated the fix, I would try to cure the error - one day it may not be so readily fixed!
If shapes are causing the error why not swap the shapes for buttons and seeing if that makes the problem go away

[scottiex]

jolivanes/kevin,
yes I'd like to find the source of the error - but most of the research I have done seems to just treat it as 'something that can happen"
Yes I could change them to buttons if you think that is a likely cause. But it is hard to know if it works because the error is one that occurs periodically. I will book that in for next roll out of a version.

All,

The recording would probably work OK and I may do that if I hit a wall, but I also like the idea of having a generalizable tool that I or someone else might be able to use one day and will continue to work even if I fiddle with the sheets or if a user had slightly edited the sheet.

this is a sheet that is rolled out to a lot of users (But I can still remotely change sheets).

So I made some decent progress with the below
but I still need some extra attributes to be defined. I can probably play around google what I need there?
The ideal solution would be to go through every attribute in a loop and copy them all....

Please Login or Register  to view this content.

At which point I save the file as an appropriate file name (I suppose I could get the macro to save it and reopen it maybe)
Then

Please Login or Register  to view this content.

feel free to tell me off for my sloppy code... I'll try to refine it.

[humdingaling]

does the problem occur when you copy the shape?

Please Login or Register  to view this content.

[Kevin#]

@humdingaling - impressed! Simple solutions are best.

[grimes0332]

Further information: https://support.microsoft.com/en-us/kb/2597034

[scottiex]

humdingaling,
Thanks,
As per Kevin I was clearly over complicating things!

The copy seems to have worked, although I'll give it a better test when I get to work and post any final result.

But it seems, even if I have any issues I can just copy from the template as opposed to gathering all the attributes which is going to be much more efficient coding anyway. So it is looking pretty good.

[scottiex]

OK so in addition to Humdingaling's code.
The below seems to work fine if I want to use an old template to refresh the buttons. (obviously just copying the buttons now)
Hopefully both solutions can be of some use to someone

Please Login or Register  to view this content.