I have an Excel 2010 workbook with three different worksheets, each of which had a number of ActiveX command buttons used to access procedures contained in an Add-In. In the middle of scoring for an international horse trials event ALL of the
command buttons disappeared from ALL of the sheets (apparently simultaneously). I subsequently wrote a macro to tell me the names, locations, sizes, and visibility of all the shapes in each worksheet. It tells me that all the command
buttons are exactly where they're supposed to be (and visible) - except that they're not, and when I run a procedure to change any property of one of them I get a message saying it can't be done. Four other identically structured workbooks
were used for scoring other competitions at the same event. In three of them no problems arose. In the remaining sheet all of the command buttons one sheet were resized to become so minute they are barely visible at 100% zoom (and one
of them disappeared altogether because its height became zero).
This application was originally developed in Excel 2003 and worked really well. It is only since "upgrading" to Excel 2010 that these issues with the command buttons have emerged. Is there a fix for these kinds of problems or do I need to give
up using ActiveX command buttons as the access route to the Add-In?
Thanks Abel, but the properties of all of these buttons ARE set to not move or size with cells.
Also, this wouldn't explain the case in which the buttons disappeared altogether (in all the worksheets of a workbook) even though Excel seems to think they are all still exactly where they are meant to be (until you try to change any of their properties
when it realises it can't do so).
The notable thing is that, of 5 identically constructed workbooks, all being subjected to the same set of procedures, the disappearing problem only occurred in one and the resizing problem occurred in only one sheet of another, which makes
it all look a bit too random for comfort.
I'm using Excel 2010 and I'm having the same problem as well. I have a workbook with about 30+ sheets. There is at least 1 button on each sheet. All buttons are set to not move or resize. When I go to print and select 'PRINT ENTIRE WORKBOOOK'. Everything
resizes and goes out of position. Default print setting is set to 'PRINT ACTIVE SHEET' so my workaround so far is to click each sheet individully and click print because this doesn't resize the buttons. But you can imagine how tedious this is getting.
I may be a bit late, only came across this post because I have a similar thing with a workbook in 2010. The thing that fixes the disappearing buttons issue for me was to format the control in question and UNCHECK Automatic size on the Alignment tab.
For me it was the print scaling that caused the problem. When configured to scale in any way the rescaling action resizes and moves the ActiveX controls to the size and position that would be on the actual printout. The problem is that Excel doesn't undo
the changes to the ActiveX controls position and size, so it looks like they magically disappear.
It seems that this rescaling happens everytime the <worksheet>.PageSetup.Pages object is accessed in any way (I was using Sheet2.PageSetup.Pages.count).
The solution is obviously to NOT scale on the Print->Page Setup under the File ribbon. Or you could have a macro function or sub that resizes and positions all ActiveX controls and call this everytime Excel screws up. This macro can then be called from
Worksheet_SelectionChange and/or Worksheet_Activate event handles.