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

User · Answer

i didn t test this  but i guess this has to do with the zoom property  maybe add activewindow Zoom   false    Also can do a loop on shapes wich defines  Placement   xlMove on workbook open and window activate   this is to prevent shapes from resizing with cells  but will move with them    i am using excel 2013 and never had this issue  also i never use it remotely     just trying to help with small ideas

User · Answer

My monitors all appear to be set at native resolutions  which deepens the mystery  However  I have found that doing SOMETHING to the button  moving or resizing  somehow fixes the problem   This routine automates the moving  and then restores the original setting   The following code seems to address the problem  at least for buttons   Public Sub StablizeButton oButton As MSForms CommandButton       With oButton            If True  then temporary distortion is remedied only by clicking somewhere            If False  then the temporary distortion is remedied merely by moving the mouse pointer away           TakeFocusOnClick   False           For best results   In the Properties Sheet  initialize to False            Left    Left   200               Move the button 200 units to the right               Left    Left - 200                   and then move it back       End With  End Sub   Invoke it as follows   Private Sub MyButton Click       StablizeButton MyButton        Remainder of code  End Sub

User · Answer

Now using Excel 2013 and this happens EVERY time I extend my display while Excel is running  and every time I remove the extension    The fix I ve started implementing is using hyperlinks instead of buttons and one to open a userform with all the other activeX controls on

User · Answer

Try changing the zoom setting to 100  using the bottom right hand slider  Fixed it for me on both monitors of different sizes

User · Answer

I had the problem also with ActiveX listboxes and found the following elsewhere and it seems to work so far  and it seems also the by far easiest solution that transfer along when handing the spreadsheet to someone else    While the ListBox has an IntegralHeight property whose side-effect of a FALSE setting will keep that control from going askew  and while command buttons have properties such as move size with cells  etc   other controls are not as graceful    And they have some more advice  http   www experts-exchange com articles 5315 Dealing-with-unintended-Excel-Active-X-resizing-quirks-VBA-code-simulates-self-correction html

User · Answer

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

User · Answer

The fixes discussed earlier  programatically resizing repositioning the Active X Controls after click events  or modifying the registry  with the D word LegacyAnchorResize   didn t solve the issue for me with Excel 2010  Windows 7 64 bit   The solution for me was found here https   support microsoft com en-us kb 838006  For Excel 2010 the link instructs to    Exit all the programs that are running  Click Start  click Run  in the Open box  type regedit  and then click OK  Locate  and then click the following registry key  HKEY CURRENT USER Software microsoft office 14 0 common On the Edit menu  point to New  and then click Key  Type Draw  and then press Enter  On the Edit menu  point to New  and then click DWORD value  Type UpdateDeviceInfoForEmf  and then press Enter Right-click UpdateDeviceInfoForEmf  and then click Modify  10 In the Value data box  type 1  and then click OK  On the File menu  click Exit to close Registry Editor

User · Answer

After searching the net  it seams the best solution is saving the file as  xlsb  binary  rather than  xlsm

User · Answer

I have the same issue sometimes  In my case  I could replicate it 100  on one file but it was inconsistent on a virtually identical file  I also found the size error wasn t permanent -- I could save and reopen the file to restore the button s appearance  I could also create a new window and then discard the damaged window   I use a single monitor configuration     For me  the button resized when I accessed the sheet s HPageBreaks collection  I was able to avoid the problem by temporarily changing the window view as follows   ActiveWindow View   xlPageBreakPreview     do pagination stuff using HPageBreaks ActiveWindow View   xlNormalView

User · Answer

I run across this issue all the time in Excel 2010  Which always brings me back to this thread  and although I haven t found a fix 100   I believe I have some information that can help others   While tediously playing around with the buttons I discovered that DIFFERENT EVENTS for each object were causing different issues   For example    Button A - The size of the button would shrink on the MouseDown event Button B - The text would enlarge on the MouseMove event  But only after the button was clicked   AKA  I click a button  code executes  leave the mouse hovering over the button  and then as soon as I move the mouse the text would enlarge  Text input box A - The text would enlarge on the MouseUp event Text input box B - The text would enlarge on the LostFocus event   The only solution that works for me is to write code to reset the size text for each object event that was causing the random resizing   Like so      Where MaterialNum is the name of the input box  and MouseDown is the event      Private Sub MaterialNum MouseDown ByVal Button As Integer  ByVal Shift As Integer  ByVal X As Single  ByVal Y As Single         Reset the size  font style  and size     With Worksheets  QuoteForm   Shapes  MaterialNum            Height   21          Width   101 25          Left   972 75          Top   87         With  DrawingObject Object Font              Name    Arial               Size   12         End With     End With  End Sub   In addition  I had to change a few options in Format Control  Right click object   Format Control     Size tab   Lock aspect ratio box is checked Properties tab  Print Object box is unchecked   Also  in the Object Properties pane  Right click object   Properties  I set TakeFocusOnClick to false  Yes  this is time consuming and tedious as it has to be done with each object  but it s the only fix that works for me  And it seems to be a quicker fix than waiting for Microsoft to fix this       Hopefully it helps others   I hope others find this helpful

User · Answer

Some of these problems may be due to controls resizing themselves when populated  I tend to the turn the IntegralHeight property of controls such as list boxes from True  default  to False to prevent automated resizing of the control height at runtime

User · Answer

Although there are clearly numerous reasons for this behavior  several answers point to issues with scaling and screen resolutions  One workaround is to use the following functions to resize and anchor the controls to a specific cell   Sub ResizeCombo ByRef cbo As Shape  ByVal rw As Long  ByVal cl As Long  ByVal wid As Long              cbo Height   Intake Cells rw  cl  Height - 1     cbo Top   Intake Cells rw  cl  Top   1     cbo Left   Intake Cells rw  cl  Left   1     cbo Width   wid     End Sub  Sub ResizeCheckbox ByRef cbo As Shape  ByVal rw As Long  ByVal cl As Long      cbo Height   Intake Cells rw  cl  Height - 1     cbo Top   Intake Cells rw  cl  Top   1     cbo Left   Intake Cells rw  cl  Left   6     cbo Width   Intake Cells rw  cl  MergeArea Width - 7         End Sub  Sub ResizeCombos       ResizeCombo Intake Shapes  School    11  1  144      ResizeCheckbox Intake Shapes  cbReading    70  1        ResizeCombo also works for option buttons     ResizeCombo Intake Shapes  obGenderMale    20  8  45     ResizeCombo Intake Shapes  obGenderFemale    20  9 50   HOWEVER  recently this workaround stopped working  I ve been banging against it for days until I discovered that the zoom level of the sheet had been adjusted to 105   Resetting it to 100  resolved the problem  However what if the user needs a higher zoom level  I figured out that I can change the zoom after resizing and things stay where they were meant to  My calling function now looks like this   Sub refresh       OldZoom   ActiveWindow Zoom     ActiveWindow Zoom   100     Call ResizeCombos     ActiveWindow Zoom   OldZoom End Sub   So far it is working

User · Answer

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

User · Answer

If you wish to not show the button moving back and forth  you can put the original placement into your code then just check against those  Turn off screen updating when button is clicked  check to see if the the placement is different on the control  change it back  if needed  turn screen updating back on  DP

User · Answer

I had a similar problem  however I found that it was quickly fixed by zooming the page in and out  Therefore  I added the below code onto the bottom of my Macro as a quick fix  I m no Excel whizz but it seems to work ok  Change the bottom number should reflect your preferred zoom   ActiveWindow Zoom   100 ActiveWindow Zoom   90

User · Answer

Grouping the controls appears to work for me  I would upvote or comment but SO won t let me   Note that this issue comes up often if you use W8 display scaling and have a high resolution laptop and older monitor

User · Answer

I may have found a fix   make sure all shapes on the worksheet have unique names   including checkboxes radio buttons  OLE controls  as well as Pictures and other shape types      Because the problem is intermittent  I can not guarantee this  I have two forms now working fine using this fix  but  two  is a very small sample set  and could just be coincidence  Never the less  these are steps I took   I listed all shapes in the problem sheet  wsForm  on a blank sheet  Sheet1    Sub ListShapes   Dim spShape As Shape Dim intRow As Integer   Sheet1 Range  A1 F1     Array  Name    Left    Top    Width    Height    Bottom Right Cell   intRow   2  For Each spShape In wsForm Shapes              Sheet1 Cells intRow  1  Value   spShape Name     Sheet1 Cells intRow  2  Value   spShape Left     Sheet1 Cells intRow  3  Value   spShape Top     Sheet1 Cells intRow  4  Value   spShape Width     Sheet1 Cells intRow  5  Value   spShape Height     Sheet1 Cells intRow  6  Value   spShape BottomRightCell Address      intRow   intRow   1  Next End Sub   I then counted each shape name in column A  using a formula in column G    COUNTIF  A 2  A 120 A2   Obviously adjust range to suit    You can then filter on all shapes that don t have a  1  in this column  The  bottom right cell  helps you find the shape on your worksheet  rename by selecting the shape  then entering new unique value in the name reference box at top left of Excel    This shape list also helped find some  dead  shapes  0 height or 0 width  which were long forgotten and unused    I hope this works for you  For that matter    I hope it works for ME for future occurrences

User · Answer

Could it be that the button is defined to stick to the corners of a cell  instead of floating freely     Check it with   Format   Properties   Object Positioning   and choose anything but  move and size with cells

User · Answer

This problem is very frustrating  my experience is that the properties are usually set properly on the ActiveX objects   I ve modified a UDF from above to just be able to run on any active sheet  this will set everything back the way it was before shrinking     Public Sub ResizeAllOfIt   Dim myCtrl As OLEObject  For Each myCtrl In ActiveSheet OLEObjects   Dim originalHeight  Dim originalWidth   originalWidth   myCtrl width  originalHeight   myCtrl height    myCtrl height   originalHeight - 1    myCtrl height   originalHeight    myCtrl width   originalWidth  Next myCtrl  End Sub

User · Answer

What I started doing a while back was to have a single sub that sets the size of all my controls   I can then run this sub from any point in the code where the controls grow or shrink   It s sometimes a lot of extra finicky work  but it keeps my buttons from eating my spreadsheet

User · Answer

It s very weird   The Width  amp  Height properties don t shrink when queried  either in code or using the properties sheet   but apparently they DO change     I noticed that if I use the properties sheet and change the width from the standard 15 to  say  14 and then BACK to 15  it fixes it   The code below works for me  and has an amusing visual effect on the sheet  you click  it shrinks  the screen flickers  and it expands back    MY SOLUTION in code  on the click event for the checkbox    Dim myCtrl As OLEObject For Each myCtrl In ActiveSheet OLEObjects   myLab   myCtrl Name   myCtrl Height   14   to  wake up  the property    myCtrl Height   15   to reset it back to normal   myCtrl Width   12   just making sure Next myCtrl

User · Answer

We just solved this on a company level by adding the following module to all macro enabled workbooks   Option Explicit  Type ButtonSizeType     topPosition As Single     leftPosition As Single     height As Single     width As Single End Type  Public myButton As ButtonSizeType  Sub GetButtonSize cb As MSForms CommandButton    Save original button size to solve windows bug that changes the button size to   adjust to screen resolution  when not in native resolution mode of screen     myButton topPosition   cb top     myButton leftPosition   cb Left     myButton height   cb height     myButton width   cb width End Sub  Sub SetButtonSize cb As MSForms CommandButton    Restore original button size to solve windows bug that changes the button size to   adjust to screen resolution  when not in native resolution mode of screen     cb top   myButton topPosition     cb Left   myButton leftPosition     cb height   myButton height     cb width   myButton width End Sub   Just call them in the beginning and end of your code like this   Private Sub CommandButton1 Click    Application ScreenUpdating   False   Turn off ScreenUpdating to make sure the user dosn t see the buttons flicker GetButtonSize CommandButton1   Saves original button size   Do cool things       SetButtonSize CommandButton1   Restores original button size  Application ScreenUpdating   True   Turn ScreenUpdating back on when you re done End Sub

User · Answer

I ve had this issue a few times and to resolve I did the following    Search through my C   drive for any file with the extension   exd  Delete those files  it is okay to do so  Implement code that re-sizes the ActiveX objects each time the sheet is opened   I found this issue was caused everything we plugged the laptop into a projector and saved the file  Then everytime the issue came up I went just repeated steps 1  and 2  and my ActiveX objects were behaving again

User · Answer

EXCEL PROFESSIONAL PLUS 2016  32BITS  VERSION 1802  BUILD 9029 2167   I was having the same issue and I could solve the problem by customizing the display scaling in windows 10  64 bits  It is extremelly simple and it works for a single user  Just follow the instructions below and you will have all the Form Controls  sheet shapes  figures and ActiveX Controls back to their original size  No need to coding or doing advanced trickery wizardry hacks  If the provided link is broken  just repeat these steps    To Set Display Custom Scaling in Windows 10  you need to do the following   Open Settings  Go to Settings - Display  On the left  click the Custom Scaling link under  Scale and Layout   The Custom Layout page will be opened  Specify a new value for scaling percent   USE 100         and voil    Close and re-open Excel    It is an old post  but I hope it helps anyone who is dealing with this annoying and stressing Excel problem   https   winaero com blog set-display-custom-scaling-windows-10

User · Answer

Old thread but I was having this issue and solved it by first grouping the  in my case  option buttons together that were having the issue then simply setting  resetting  the size of this group in an Auto Open as such   With ThisWorkbook      Worksheets  1   Shapes  grpInput   Width   383 1      Worksheets  2   Shapes  grpSuite   Width   383 1      Worksheets  3   Shapes  grpLoc   Width   383 1      Worksheets  4   Shapes  grpDecision   Width   383 1 End With

User · Answer

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

User · Answer

I find that the problem only seems to happen when freeze panes is turned on  which will normally be on in most apps as you will place your command buttons etc in a location where they do not scroll out of view   The solution that has worked for be is to group the controls but also ensuring that the group extends beyond the freeze panes area  I do this by adding a control outside the freeze panes area  add it into the group but also hide the control so you don t see it

User · Answer

This problem is in fact due to screen resolution  Most commonly it occurs when the user connects to a projector  or WebEx while using the excel application   A simple solution to this problem is to ask the user to reboot their machine without any peripheral connections  projector  and then open the excel application again

User · Answer

I noticed that none of these answers anchor the control to a specific row and column  This has worked pretty cleanly for me as Rows Columns tend to be more predictable than monitors  resolution    The below sample basically  measures  where the object should go  then sets it there in the Worksheet Activate code  In the below example  the button will always snap to covering D8 F10    Private Sub Worksheet Activate    Note this is done in the sheet code  not a Module      With Me Shapes  CommandButton1            Left   Me Range  A1 C1   Width         Top   Me Range  a1 a7   Height         Width   Me Range  D1 F1   Width         Height   Me Range  A8 a10   Height     End With  End Sub   The result will appear as shown below

User · Answer

Add this code to a  reg file  Double-click and and confirm  Restart Excel    Windows Registry Editor Version 5 00   HKEY CURRENT USER Software Microsoft Office 14 0 Excel options   LegacyAnchorResize  dword 00000001   HKEY CURRENT USER Software Microsoft Office 14 0 Common Draw   UpdateDeviceInfoForEmf  dword 00000001

User · Answer

I ve found a fix that works  and solves the problem for a single user  If you don t want to read my little rant you can skip straight to the solution   RANT   I ve been experiencing this stupid problem since the dinosaurs  In the meantime  Microsoft have had plenty of resources to release countless major updates to the Office suite and yet this problem goes unaddressed  It s absolutely infuriating  I have no reason whatsoever to upgrade when basic stuff like this doesn t work  Surely someone at MS uses ActiveX controls in Excel right on a high-res display  no   I didn t experience this issue on my desktop PC  no clue as to why  but on my Surface Pro 4 my ActiveX controls go bananas whenever I click them   Today I decided to get to the bottom of this  I searched high and low  tried a every solution proposed on various forums  none of which works by the way   It doesn t matter if the controls are grouped or not  locked or not  hotfix installed or not   Yesterday I had another problem with things misbehaving in another app called Traktor  DJ software   where controls would jump around when display scaling was set to a value that was not an exact multiple of 100   A user found the solution was to edit the compatibility mode for this application  So I did the same for Excel  and it worked  Now my controls stay put  regardless of display resolution and scaling   SOLUTION    Ensure Excel is not running    Locate EXCEL EXE  on my system this can be found at C  Program Files Microsoft Office Office16 EXCEL EXE  Rename  EXCEL EXE  to   EXCEL EXE   basically change it to something else  Right-click renamed EXCEL EXE file  then go to Properties   Compatiblity tab   Settings section Set Override high DPI scaling behaviour   Enabled  and Scaling performed by   Application Click OK Rename to   EXCEL EXE  back to  EXCEL EXE    Now Excel will run at its native resolution and ActiveX controls won t go awry  The only downside is that Excel won t respond to screen scaling  so things may look a little smaller than one would like  On my Surface Pro 4 is more than acceptable  NOTES   1  Steps 2 and 6 are required with Excel 2016  because the Properties dialog for EXCEL EXE does not offer the Compatibility tab  After renaming  the tab becomes available   2  This solution only works on a one-user basis  That is  if you send an Excel file containing ActiveX controls to your colleagues  the ActiveX controls won t display correctly on their system unless they change the compatibility mode settings   3  After applying this hack  previously corrupted Excel files appear to fix themselves when you open them  with all controls recovering their original intended dimensions   Please test and comment  I hope this can help someone  cheers

User · Answer

I have found this problem across all objects  buttons text boxes etc   When I print print preview multiple sheets  all the sheets except for the first in the sequence have moved or resized objects  After much testing  the simplest solution in to page zoom up or down and return to the original setting

User · Answer

After trying many of the solutions on this and related posts  I discovered that the solution to a different problem that Microsoft had posted also worked reliably for this particular issue when applied in a particular way  at least within the context I m working within  I experienced these problems in a resolution native to the laptops we have here and on a PC whenever I d switch to a non-native resolution  Namely  buttons loading at a larger than normal size  getting larger when clicked on  and text and images in these buttons shrinking  and at least the setting for the text remained the same so I couldn t see any way to change it back programmatically or otherwise   These have not been intermittent problems in my case  I have had intermittent problems though after using print preview which Microsoft has posted a solution for here  I ve tried this and it seems to be working   The solution  For Excel  close the app and delete the MSForms exd file from C  Users UserName  AppData Local Temp Excel8 0 while in the resolution that you want to view the buttons in  You could also search for other  exd files in your local AppData Temp folder for other Office apps   As I mentioned  this is a solution proposed by Microsoft for a different issue - where the buttons stop working after the Dec  14 updates are installed  Here s the link to that article  In that case  it s a one time fix  Here  you may need to do it every time you change resolutions

User · Answer

We had this issue as well  I cannot remember exactly what fixed as we are now functioning as expected    try grouping the controls  I think that was our resolution

[excel] Excel: the Incredible Shrinking and Expanding Controls

Examples related to excel

Examples related to vba

Examples related to excel-2010

Examples related to excel-2007

Examples related to excel-2003