vba Excel：令人难以置信的收缩和扩展控制

Excel: the Incredible Shrinking and Expanding Controls

提问by Nick

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?

有没有其他人经历过这种快乐？

采纳答案by matt_black

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

该问题似乎与 Windows 在显示器上处理非本机分辨率的方式有关，可以通过多种方式避免

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.

我们最近一直在测试一个由几十人使用的 excel 工作表，并且已经对原因和一些可能的修复有了很好的了解。

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 duplicatethe display rather than extendingit (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 alwayscause 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.

原因似乎与任何以非原始分辨率使用屏幕的设置有关。如果用户将外接显示器插入笔记本电脑并且没有仔细选择生成的屏幕配置，则很容易发生这种情况。例如，如果将笔记本电脑插入投影仪（可能是带有原生 1024 x 768 显示器的旧笔记本电脑）但笔记本电脑是 1280 x 800 并且用户选择复制显示器而不是扩展它（“连接到”中的设置） Windows 7 中的投影仪”或“显示”控制面板），结果是在两个屏幕上以非本机分辨率显示不可预测且通常不令人满意的图像。我们发现这些设置几乎总是导致 Excel 按钮出现严重问题，尤其是 ActiveX 控件。有时，在重复点击时，它们会缩小到不可读；其他时候它们会扩展以覆盖整个屏幕。

Mostly, when we instruct users to use the extenddisplay 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.

还有一些基于代码的方法可以最小化问题。我们尝试在点击按钮和控件时重置它们的位置和大小（如果你有很多按钮，这会增加很多乏味的代码）。这有时奏效。我们还尝试将 autosize 属性从 true 切换到 false 并返回（这在开发人员模式下手动工作），这修复了更多实例，但显然不是全部。

回答by Nick

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.

发现原因是人们在通过远程桌面访问的机器上打开电子表格，并且本地和远程机器之间的屏幕分辨率存在差异。这会影响控件工具箱中可用的控件，但尚未使用旧式表单按钮控件遇到问题，所以我不满意的答案是使用它。

回答by peterg4000

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:

这一直困扰着我多年，断断续续。周围有许多修复程序，但它们似乎很受欢迎。它仍在 Excel 2010 中发生（发生在我 2014 年 5 月）中，并且某些报告仍在 Excel 2013 中发生。我发现至少符合我的情况的最佳描述（Excel 2010，不涉及 RDP，不涉及打印预览）在这里：

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

Microsoft Excel 支持团队博客：ActiveX 和表单控件在单击或执行打印预览时会自行调整大小（在 Excel 2010 中）

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

（这可能对 Excel 2013 的用户没有帮助，抱歉）

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

编辑：添加细节以防 technet 链接失效，technet 文章说：

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

首先为 Excel 2010 安装 Microsoft Hotfix 2598144，可用：此处。

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:

其次，如果您的症状是“在 Excel 2010 工作表中单击后，ActiveX 按钮更改为不正确的大小”，那么您应该：

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

• 单击开始，单击运行，在打开框中键入 regedit，然后单击确定。
• 找到并选择以下注册表子项 HKEY_CURRENT_USER\Software\Microsoft\Office\14.0\Excel\Options
• 在编辑菜单上，指向新建，然后单击 DWORD（32 位）值。
• 键入 LegacyAnchorResize，然后按 Enter。
• 在“详细信息”窗格中，右键单击 LegacyAnchorResize，然后单击“修改”。
• 在数值数据框中，键入 1，然后单击确定。
• 退出注册表编辑器。

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:

或者其次，如果您的症状是“在 Excel 2010 中查看工作簿的打印预览后，工作簿中的按钮表单控件显示不正确”，那么您应该：

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

• 单击开始，单击运行，在打开框中键入 regedit，然后单击确定。
• 找到并选择以下注册表子项：HKEY_CURRENT_USER\Software\Microsoft\Office\14.0\Excel\Options
• 在编辑菜单上，指向新建，然后单击 DWORD（32 位）值。
• 键入 MultiSheetPrint，然后按 Enter。
• 在详细信息窗格中，右键单击 MultiSheetPrint，然后单击修改。
• 在数值数据框中，键入 1，然后单击确定。
• 再次选择以下注册表子项：HKEY_CURRENT_USER\Software\Microsoft\Office\14.0\Excel\Options
• 在编辑菜单上，指向新建，然后单击 DWORD（32 位）值。
• 键入 LegacyAnchorResize，然后按 Enter。
• 在“详细信息”窗格中，右键单击 LegacyAnchorResize，然后单击“修改”。
• 在数值数据框中，键入 1，然后单击确定。
• 退出注册表编辑器。

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:

或者其次，如果您的症状是“在查看工作表的打印预览后，在 Excel 2010 工作表中，ActiveX 按钮更改为不正确的大小”，那么您应该：

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

• 单击开始，单击运行，在打开框中键入 regedit，然后单击确定。
• 找到并选择以下注册表子项：HKEY_CURRENT_USER\Software\Microsoft\Office\14.0\Excel\Options
• 在编辑菜单上，指向新建，然后单击 DWORD（32 位）值。
• 键入 LegacyAnchorResize，然后按 Enter。
• 在“详细信息”窗格中，右键单击 LegacyAnchorResize，然后单击“修改”。
• 在数值数据框中，键入 1，然后单击确定。
• 退出注册表编辑器。

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

祝你好运。这个问题好痛苦。。。

回答by TheG

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.

这个问题实际上是由于屏幕分辨率造成的。最常见的情况是用户在使用 Excel 应用程序时连接到投影仪或 WebEx。

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.

这个问题的一个简单解决方案是要求用户在没有任何外围连接（投影仪）的情况下重新启动他们的机器，然后再次打开 excel 应用程序。

回答by baffled68

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.

前面讨论的修复程序，在单击事件后以编程方式调整 Active X 控件的大小/重新定位，或修改注册表（使用 D 字 LegacyAnchorResize），并没有解决我使用 Excel 2010/Windows 7 64 位的问题。

The solution for me was found here:https://support.microsoft.com/en-us/kb/838006

在这里找到了适合我的解决方案：https: //support.microsoft.com/en-us/kb/838006

For Excel 2010 the link instructs to:

对于 Excel 2010，该链接指示：

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.

1. 退出所有正在运行的程序。
2. 单击开始，单击运行，在打开框中键入 regedit，然后单击确定。
3. 找到并单击以下注册表项：
4. HKEY_CURRENT_USER\Software\microsoft\office\14.0\common
5. 在编辑菜单上，指向新建，然后单击项。
6. 键入 Draw，然后按 Enter。
7. 在编辑菜单上，指向新建，然后单击 DWORD 值。
8. 键入 UpdateDeviceInfoForEmf，然后按 Enter
9. 右键单击 UpdateDeviceInfoForEmf，然后单击修改。10.在数值数据框中，键入 1，然后单击确定。
10. 在文件菜单上，单击退出以关闭注册表编辑器。

回答by Joe Stellato

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.

这个问题非常令人沮丧，我的经验是通常在 ActiveX 对象上正确设置属性。我已经从上面修改了 UDF，使其能够在任何活动工作表上运行，这将使所有内容恢复到收缩前的状态。

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

回答by PGSystemTester

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_Activatecode. In the below example, the button will always snap to covering D8:F10.

下面的示例基本上“测量”对象应该去的位置，然后在Worksheet_Activate代码中将其设置在那里。在下面的示例中，按钮将始终对齐到 cover 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:

结果将如下所示：

回答by Paul Margus

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

回答by jfkelley

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.

我注意到，如果我使用属性表并将宽度从标准的 15 更改为，比如说，14，然后返回到 15，它会修复它。

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

回答by Robitron

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

1. 在我的 C:\ 驱动器中搜索扩展名为“.exd”的任何文件
2. 删除那些文件（这样做是可以的）
3. 实现每次打开工作表时重新调整 ActiveX 对象大小的代码

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

我发现这个问题是由我们将笔记本电脑插入投影仪并保存文件引起的。然后每次出现问题时，我都会重复步骤 1. 和 2. 并且我的 ActiveX 对象再次运行