Excel: the Incredible Shrinking and Expanding Controls

Question

Occasionally, I'll happen across a spreadsheet which suffers from magic buttons or listboxes which get bigger or smaller over time.

Nothing in the code is instructing this.

Has anybody else experienced this joy?

Answer 1

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.