Unable to Service Request

Question

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? 

Answer 1

This may be because the controls are resizing when the cells are being resized or deleted.

 

Try this:

Right click on the control --> Format control --> Properties --> Don't move or size with cells

If this answer helps please mark it as the answer.

---

- Abel

Answer 2

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.

 

Ben

Answer 3

I

I have no solution only the very same problem. A sheet developed previously is being redeveloped on 2010 and when saved pull downs become invisible to the naked eye.

While the appear gone a Find & Select -> Goto  Special -> Objects shows them alive and well in the sheet but wit zero height and moved from where they belong...

 

If someone knows a fix please advise... I have contacted MSFT support and if they find a fix i will post same...

 

Makes the easy very difficult......

Answer 4

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.

Answer 5

Same thing here, 2010 with many macro's attached to several different buttons. When printing with a macro the buttons shrink and then move. No problems in 2007.

Answer 6

Hi,

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.

Hope this helps.

Greg

Answer 7

Nope, that didn't help either.

What does MICROSOFT have to say?????

Answer 8

A similar post was made regarding this a while ago. They do have a hot-fix for in-sheet control resizing. This is a known issue with Excel 2010.

After attempted fixes nearly drove me crazy, I now stay away from in-sheet controls....and do all of my GUI in forms. That's where they belong anyway.

Answer 9

I had the same problem.

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.

BR