Last week someone sent me an Excel file that was having problems – it wouldn’t save properly, and there were a few other strange behaviours. The file had been working well for a few years, but recently started acting up.
The file was used in a factory, where the technicians filled in data, and printed the file, a few times each day. To print, they clicked a button on the data entry sheet. A macro printed the data entry sheet, copied the latest data to a storage sheet, and cleared the data entry cells.
When I tried to open the file, Excel 2010 warned me that the file could be dangerous – not a good sign!
I ran the file through my virus scanner, and nothing malicious was found, so I opened the file in Excel 2003. No complaints from that version.
What’s Hiding Under There?
When I switched to the storage sheet, there was some mysterious flickering, 2 buttons, and a partially covered button. I moved all 3 buttons, to see what was under them. Surprise! There were more buttons, and below those, more buttons.
With a bit of code, I did a quick count of the buttons on that worksheet.
Under the 3 visible buttons, there were almost 7000 buttons. Yikes!
Every time the original data was copied and pasted onto this sheet, the 2 original buttons were being copied and pasted on top of the previous buttons. No wonder the workbook was having problems!
Fix the Problem
To clean up the storage sheet, I deleted all of the buttons, except one copy of each.
Then on the data entry sheet, I changed the button settings, so they don’t move or size with the cells.
- Right-click on the button (these are buttons from the Form Control toolbox)
- Click Format Control
- On the Properties tab, select ‘Don’t Move or Size with Cells’, and click OK
Now, if the data entry sheet is copied and pasted, the buttons won’t be included.
If you’re copying and pasting cells, day after day, remember to check the settings for any buttons, or other shapes, that are on those cells. Don’t get buried under a mountain of buttons!