Excel: the Incredible Shrinking and Expanding Controls [closed]

by

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.

More Related Contents:

  1. filter out multiple criteria using excel vba
  2. Cannot VBA write data to cells in Excel 2007/2010 within a function
  3. How to add a custom Ribbon tab using VBA?
  4. What is the difference between “Form Controls” and “ActiveX Control” in Excel 2010?
  5. How to use workbook.saveas with automatic Overwrite
  6. Closing Excel Application using VBA
  7. Loop through each row of a range in Excel
  8. How to add parameters to an external data query in Excel which can’t be displayed graphically?
  9. Excel: Searching for multiple terms in a cell
  10. Excel error 1004 “Unable to get …. property of WorksheetFunction class” appearing inconsistently
  11. Find last row in range
  12. VBA equivalent to Excel’s mod function
  13. Excel VBA to match and line up rows
  14. Excel Date Conversion from yyyymmdd to mm/dd/yyyy
  15. Excel VBA Run-time error ’13’ Type mismatch
  16. Excel VBA wildcard search
  17. VBA Excel – IFERROR & VLOOKUP error
  18. How to programmatically code an ‘undo’ function in Excel-Vba? [duplicate]
  19. How to check whether certain sheets exist or not in Excel-VBA? [duplicate]
  20. using a sheet in an excel user defined function
  21. Delete all data rows from an Excel table (apart from the first)
  22. How does one restore default case to a variable in VBA (Excel 2010)?
  23. How to do a “Save As” in vba code, saving my current Excel workbook with datestamp?
  24. Excel VBA Run-time Error ‘32809’ – Trying to Understand it
  25. Save excel file multiple times with file names in another workbook.
  26. How do I find the last column with data?
  27. Excel VBA: Answer gets “stuck”
  28. Excel VBA – Range.Copy transpose paste
  29. Open an Excel file from SharePoint site
  30. VBA: Modify chart data range

Leave a Comment