Excel: the Incredible Shrinking and Expanding Controls [closed]

The problem seems to relate to the way Windows handles non-native resolutions on monitors and can be avoided in several ways

The problem can be a complete nightmare when it happens, but it only happens intermittently.

We have been testing recently an excel worksheet used by a few dozen people and have developed a good idea of the cause and some possible fixes.

The cause seems to relate to any setup where screens are used in something other than their native resolution. This can happen easily if a user plugs an external monitor into a laptop and doesn't choose the resulting screen configuration carefully. For example, if a laptop is plugged into a projector (perhaps an old one with a native 1024 by 768 display) but the laptop is a 1280 by 800 and the user chooses to duplicate the display rather than extending it (settings in "connect to a projector" or "displays" control panel in Windows 7), the result is an unpredictable and usually unsatisfactory image on both screens with both in non-native resolutions. We have found that these settings almost always cause serious problems with Excel buttons, especially ActiveX controls. Sometimes, on repeated clicks, they shrink to unreadability; other times they expand to cover the whole screen.

Mostly, when we instruct users to use the extend display setting and the result is two screens both using native resolutions, we don't see the problem.

There are also code-based ways to minimize the problem. We tried resetting the location and size of buttons and controls when they were clicked (which adds a lot of tedious code if you have a lot of buttons). This sometimes worked. We also tried toggling the autosize property from true to false and back (this works manually in developer mode) and this fixes more instances, but not apparently all.

found the cause to be people opening the spreasheet on a machine accessed via Remote Desktop, and there being a difference in screen resolution between local and remote machines. This affects the controls available from the control toolbox, but yet to experience the issue using an old school forms button control, so my unsatisfactory answer is to use that.

This has been plaguing me for years, on and off. There are a number of fixes around, but they seem hit and miss. It was still occurring in Excel 2010 (happening to me May 2014), and is still occurring in Excel 2013 by some reports. The best description I have found that matches my situation at least (Excel 2010, no RDP involved, no Print Preview involved) is here:

Microsoft Excel Support Team Blog: ActiveX and form controls resize themselves when clicked or doing a print preview (in Excel 2010)

(This might not help for users of Excel 2013 sorry)

EDIT: Adding detail in case technet link ever goes dead, the technet article says:

FIRSTLY install Microsoft Hotfix 2598144 for Excel 2010, available: here.

SECONDLY, if your symptom is "An ActiveX button changes to the incorrect size after you click it in an Excel 2010 worksheet", then you should:

• Click Start, click Run, type regedit in the Open box, and then click OK.
• Locate and then select the following registry subkey HKEY_CURRENT_USER\Software\Microsoft\Office\14.0\Excel\Options
• On the Edit menu, point to New, and then click DWORD (32-bit) value.
• Type LegacyAnchorResize, and then press Enter.
• In the Details pane, right-click LegacyAnchorResize, and then click Modify.
• In the Value data box, type 1, and then click OK.
• Exit Registry Editor.

OR SECONDLY, if your symptom is "A button form control is displayed incorrectly in a workbook after you view the print preview of the workbook in Excel 2010", then you should:

• Click Start, click Run, type regedit in the Open box, and then click OK.
• Locate and then select the following registry subkey: HKEY_CURRENT_USER\Software\Microsoft\Office\14.0\Excel\Options
• On the Edit menu, point to New, and then click DWORD (32-bit) value.
• Type MultiSheetPrint, and then press Enter.
• In the Details pane, right-click MultiSheetPrint, and then click Modify.
• In the Value data box, type 1, and then click OK.
• Select the following registry subkey again: HKEY_CURRENT_USER\Software\Microsoft\Office\14.0\Excel\Options
• On the Edit menu, point to New, and then click DWORD (32-bit) value.
• Type LegacyAnchorResize, and then press Enter.
• In the Details pane, right-click LegacyAnchorResize, and then click Modify.
• In the Value data box, type 1, and then click OK.
• Exit Registry Editor.

OR SECONDLY, if your symptom is "An ActiveX button is changed to an incorrect size in an Excel 2010 worksheet after you view the print preview of the worksheet", then you should:

• Click Start, click Run, type regedit in the Open box, and then click OK.
• Locate and then select the following registry subkey: HKEY_CURRENT_USER\Software\Microsoft\Office\14.0\Excel\Options
• On the Edit menu, point to New, and then click DWORD (32-bit) value.
• Type LegacyAnchorResize, and then press Enter.
• In the Details pane, right-click LegacyAnchorResize, and then click Modify.
• In the Value data box, type 1, and then click OK.
• Exit Registry Editor.

Good luck. This issue is such a pain...