Macro to delete all objects except charts

[Mathematicus]

Hello all, I am new to the forum, so please bear with me. I am working on a spreadsheet that is an absolute nightmare; it locks up frequently and takes upwards of 20 minutes to save. It is about 16MB with 164 sheets. It looks like the original creator used templates with embedded objects, but I am not sure. I believe that at least part of the problem is hidden objects. For some reason, on each sheet there are 24 extra unused buttons (stacked) and approximately 100 unused text boxes. Each sheet also contains a chart. I need to delete all objects in the workbook except charts. I have tried F5>GoTo>Special>Objects and ctrl+click on the chart. It works fine for maybe two worksheets, and then the spreadsheet locks up. I have searched high and low for a macro that will delete all object except charts, but i am having no luck. I am not nearly proficient enough in VBA to do this myself. Could anyone help me out here? If you have any other suggestions for cleaning up this spreadsheet, those would be more than welcome as well. Thanks.

[Leith Ross]

Hello Mathematicus,

Welcome to the Forum!

This macro will all Forms type and ActiveX, aka Control Toolbox, controls. All other object are left intact: Charts, comments, pictures, workbook links, etc. Copy this code to standard VBA module in your workbook.

Please Login or Register  to view this content.

Adding the Macro (Excel 95 - 2003)

1. Copy the macro above pressing the keys CTRL+C
2. Open your workbook
3. Press the keys ALT+F11 to open the Visual Basic Editor
4. Press the keys ALT+I to activate the Insert menu
5. Press M to insert a Standard Module
6. Paste the code by pressing the keys CTRL+V
   
7. Make any custom changes to the macro if needed at this time.
8. Save the Macro by pressing the keys CTRL+S
9. Press the keys ALT+Q to exit the Editor, and return to Excel.

To Run the Macro...
To run the macro from Excel, open the workbook, and press ALT+F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

[Mathematicus]

Thank you for the quick reply. I ran the macro on my spreadsheet and it cleared out my buttons and text boxes. Unfortunately, this did not have the effect that I was hoping for. The spreadsheet actually increased in size by 0.2 MB, bringing it to 16.8 MB total. I could really care less about the size, I just need it to work properly without crashing every five minutes.

I suppose I could give some more details on the spreadsheet and its behavior, and maybe that will help. This spreadsheet is used to track company metrics, and I have recently been given responsibility for it. It consists of 164 sheets, and all but 7 of them (dashboards) have an embedded chart. Additionally, each sheet with a chart has 10 cells with identical if-then statements and those cells also have conditional formatting (cell color). There are several macros used in the workbook, but they are only used to navigate from sheet to sheet from the dashboards. I have also noticed that the spreadsheet has more than doubled in size since March. Since it is a shared workbook, I tried removing all connected users, saving, and then re-opening (as suggested somewhere on the internet), but the file size never changed and functionality is still not restored. There were also instances where the conditional formatting got out of hand somehow. Some sheets had over 200 rules when only four were needed (Red, Green, Blue, Yellow). They seem to have been duplicated somehow. I corrected all the conditional formatting, but still no luck. I just ran the above macro, and I am still in the same boat. Any suggestions?

P.S. If I need to post this in a new thread since I am slightly off topic, please let me know.

[snb]

or ?

Please Login or Register  to view this content.