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.
Danger!
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.
Debug.Print ActiveSheet.Shapes.Count
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!
__________
Hi Deb
A great tip for everyone.
I recently encountered the same problem myself, and it took me quite q while to work out that it was those pesky buttons being copied.
LOL, don’t you love working on other people’s workbooks!
I once found a similar thing in Access. I added a query to someone else’s database, but the join did not seem to work properly. Then I looked at the relationship diagram, selected a relationship, deleted it, found another relationship underneath that, deleted it… went on for nine levels. I still don’t know how they did that.
Thanks Roger and Ed! Glad I’m not the only one with these challenges. 😉