I developed a worksheet in Excel for my office several years ago that we use on a regular basis. It is a workhorse of a spreadsheet that we use to input all the energy and water usage for a facility, and then can tabulate, graph, and manipulate the data. Since all this data takes up a good bit of real estate on the page, I developed a lot of macros that are designed for navigation within the spreadsheet (e.g., “View Fuel 1 Usage”, “Enter Gas Usage”), as well as several to print the graphs and tables. Since there are a lot of these macros, I just made a table of Visual Basic buttons at the top of the worksheet, so that the user can easily find the button to print a specific chart or move to the spot on the spreadsheet to input a specific set of data.
We’ve been using this same basic file with no problems for several years. I have archived the basic file; when we want to use it, we copy the file, and rename it.
Within the past couple of months, all of the users of this file have come to me with the same problem. When the user selects a particular button, the macro still runs successfully. Now, however, my nice, neat, organized table of macro buttons at the top of the page has been turned into a mess. It looks as if a stack of dominoes had been shoved over to the left hand side of the worksheet, with buttons stacked on top of other buttons. If you then decide to run another one of the buttons (one you find on the top of the stack), the macro still runs successfully, but the pileup gets worse.
I do not believe that it is a problem with corruption in my source file, because I have pulled up old projects from six months or more ago that never had these problems before, and the problem now occurs in them as well.
We suspect that this is a problem caused by an Excel update. As office policy, we all do Windows and other Microsoft office product updates on a regular basis. This is the only culprit that we can think of.
Has anyone else experienced this, or do you have any suggestions?
I’ve never had that problem but what I’d try first is deleting all the buttons and creating just a couple of new ones to see if those stay in place. If they’re ok, re-create the rest of them.
I may insert a new worksheet within the spreadsheet and insert a few new buttons to see if it does the same thing to them. I was hoping to avoid having to re-create the existing buttons, though, because I have about 60-80 or so of them spread throughout the spreadsheet.
I have the feeling that the same thing will happen to new buttons, but I’m not certain. We fear that either an Excel update or a Visual Basic update has done this to us, so that it will do the same thing with any buttons. Perhaps there is now some command in VB that I need to set to make sure the buttons stay anchored in one place, whereas I just had to locate them in the right place the first time around.
For now, now that we all know about it, we just make sure we save the file before running any print macros, and then close the file immediately without saving afterwards. (And curse a lot if we forget, and automatically save the file out of force of habit.) I can still get to the buttons if I go into VBasic design mode, and then manually pull the other buttons out of the way first, but it’s irritating to have to go on a hunting expedition instead of having a nice, neat table. These buttons were supposed to be time savers.
In “design mode” if you right-click on a button | Format Control… | Properties Tab --> What’s selected under object positioning? Maybe your cells are changing and that’s changing your button position?
Hmmm. I’ll check that when I’m back in the office on Monday.
These particular cells shouldn’t be changing. They’re just there holding space for the buttons, and don’t contain any data.
It’s annoying to watch it happening. You hit the “Print Elec Graph” button, and it relocates itself to the electric graph portion of the worksheet, highlights the area, spits out a message that it’s printing your graph, and then runs back up to the top of the page where it show you the buttons beautifully lined up in all their pristine glory---- and laughs at you while you watch it then scramble them.
Well, maybe it doesn’t do the last part. But I wouldn’t swear it.
