Excel: the Incredible Shrinking and Expanding Controls [closed]

Closed. This question does not meet Stack Overflow guidelines. It is not currently accepting answers.

---

开发者_运维百科

This question does not appear to be about a specific programming problem, a software algorithm, or software tools primarily used by programmers. If you believe the question would be on-topic on another Stack Exchange site, you can leave a comment to explain where the question may be able to be answered.

Closed 2 years ago.

Improve this 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?

---

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...

---

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.

---

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:

1. Exit all the programs that are running.
2. Click Start, click Run, in the Open box, type regedit, and then click OK.
3. Locate, and then click the following registry key:
4. HKEY_CURRENT_USER\Software\microsoft\office\14.0\common
5. On the Edit menu, point to New, and then click Key.
6. Type Draw, and then press Enter.
7. On the Edit menu, point to New, and then click DWORD value.
8. Type UpdateDeviceInfoForEmf, and then press Enter
9. Right-click UpdateDeviceInfoForEmf, and then click Modify. 10.In the Value data box, type 1, and then click OK.
10. On the File menu, click Exit to close Registry Editor.

---

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

---

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

---

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:

---

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"

---

It's very weird. The Width & 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

---

I've had this issue a few times and to resolve I did the following:

1. Search through my C:\ drive for any file with the extension '.exd'
2. Delete those files (it is okay to do so)
3. 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

---

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

---

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

---

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

---

[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.

1. To Set Display Custom Scaling in Windows 10, you need to do the following.

   Open Settings.

2. Go to Settings - Display.
3. On the left, click the Custom Scaling link under "Scale and Layout".
4. 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/

---

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

---

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

---

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

---

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.

---

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

---

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...

---

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

---

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.

---

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.

---

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.

---

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

---

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.

---

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.

---

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

---

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)

1. Locate EXCEL.EXE (on my system this can be found at C:\Program Files\Microsoft Office\Office16\EXCEL.EXE)
2. Rename "EXCEL.EXE" to "_EXCEL.EXE" (basically change it to something else)
3. Right-click renamed EXCEL.EXE file, then go to Properties > Compatiblity tab > Settings section
4. Set Override high DPI scaling behaviour = Enabled, and Scaling performed by = Application
5. Click OK
6. 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!