单击 VBA Excel 按钮后会调整大小（命令按钮）

VBA Excel Button resizes after clicking on it (Command Button)

提问by HHeckner

How can I stop a button from resizing? Each time I click on the button, either the size of the button or the font size changes.

如何阻止按钮调整大小？每次单击按钮时，按钮的大小或字体大小都会发生变化。

Note: I cannot lock my sheet as my Macro will write into the sheet.

注意：我无法锁定我的工作表，因为我的宏会写入工作表。

Autosize is turned off. I run Excel 2007 on Windows 7 (64 Bit).

自动调整大小已关闭。我在 Windows 7（64 位）上运行 Excel 2007。

回答by Jean-Fran?ois Corbett

I use the following for ListBoxes. Same principle for buttons; adapt as appropriate.

我使用以下列表框。按钮原理相同；适当地适应。

Private Sub myButton_Click()
    Dim lb As MSForms.ListBox
    Set lb = Sheet1.myListBox

    Dim oldSize As ListBoxSizeType
    oldSize = GetListBoxSize(lb)

    ' Do stuff that makes listbox misbehave and change size.
    ' Now restore the original size:
    SetListBoxSize lb, oldSize
End Sub

This uses the following type and procedures:

这使用以下类型和过程：

Type ListBoxSizeType
    height As Single
    width As Single
End Type

Function GetListBoxSize(lb As MSForms.ListBox) As ListBoxSizeType
    GetListBoxSize.height = lb.height
    GetListBoxSize.width = lb.width
End Function

Sub SetListBoxSize(lb As MSForms.ListBox, lbs As ListBoxSizeType)
    lb.height = lbs.height
    lb.width = lbs.width
End Sub

回答by TObyFish

I added some code to the end of the onClickthus:

我在最后添加了一些代码onClick：

CommandButton1.Width = 150
CommandButton1.Height = 33
CommandButton1.Font.Size = 11

Seems to work.

似乎工作。

I got the issue a slightly different way. By opening the workbook on my primary laptop display, then moving it to my big monitor. Same root cause I would assume.

我以稍微不同的方式解决了这个问题。通过在我的主要笔记本电脑显示器上打开工作簿，然后将其移动到我的大显示器上。我会假设相同的根本原因。

回答by Aeggie78

(Excel 2003)

(Excel 2003)

It seems to me there are two different issues: - resizing of text of ONE button when clicking on it(though not always, don't know why), and - changing the size of ALL buttons, when opening the workbook on a display with a different resolution (which subsist even when back on the initial display).

在我看来，有两个不同的问题： - 单击一个按钮时调整其文本大小（虽然并非总是如此，不知道为什么），以及 - 在显示器上打开工作簿时更改所有按钮的大小不同的分辨率（即使回到初始显示时仍然存在）。

As for the individual resizing issue: I found that it is sufficient to modify one dimension of the button to "rejuvenate" it. Such as :

至于个别调整大小问题：我发现修改按钮的一个维度来“恢复”它就足够了。如 ：

 myButton.Height = myButton.Height + 1
 myButton.Height = myButton.Height - 1

You can put it in each button's clicking sub ("myButton_Click"), or implement it a custom Classe for the "onClick" event.

你可以把它放在每个按钮的点击子（“myButton_Click”）中，或者为“onClick”事件实现一个自定义类。

回答by user6645884

Seen this issue in Excel 2007, 2010 and 2013

在 Excel 2007、2010 和 2013 中看到此问题

This code prevents the issue from manifesting. Code needs to run every time a active X object is activated.

此代码可防止问题出现。每次激活一个活动的 X 对象时都需要运行代码。

Sub Shared_ObjectReset()

    Dim MyShapes As OLEObjects
    Dim ObjectSelected As OLEObject

    Dim ObjectSelected_Height As Double
    Dim ObjectSelected_Top As Double
    Dim ObjectSelected_Left As Double
    Dim ObjectSelected_Width As Double
    Dim ObjectSelected_FontSize As Single

    ActiveWindow.Zoom = 100

    'OLE Programmatic Identifiers for Commandbuttons = Forms.CommandButton.1
    Set MyShapes = ActiveSheet.OLEObjects
    For Each ObjectSelected In MyShapes
        'Remove this line if fixing active object other than buttons
        If ObjectSelected.progID = "Forms.CommandButton.1" Then
            ObjectSelected_Height = ObjectSelected.Height
            ObjectSelected_Top = ObjectSelected.Top
            ObjectSelected_Left = ObjectSelected.Left
            ObjectSelected_Width = ObjectSelected.Width
            ObjectSelected_FontSize = ObjectSelected.Object.FontSize

            ObjectSelected.Placement = 3

            ObjectSelected.Height = ObjectSelected_Height + 1
            ObjectSelected.Top = ObjectSelected_Top + 1
            ObjectSelected.Left = ObjectSelected_Left + 1
            ObjectSelected.Width = ObjectSelected_Width + 1
            ObjectSelected.Object.FontSize = ObjectSelected_FontSize + 1

            ObjectSelected.Height = ObjectSelected_Height
            ObjectSelected.Top = ObjectSelected_Top
            ObjectSelected.Left = ObjectSelected_Left
            ObjectSelected.Width = ObjectSelected_Width
            ObjectSelected.Object.FontSize = ObjectSelected_FontSize

        End If
    Next

End Sub

回答by Mike Repko

Found the same issue with Excel 2016 - was able to correct by changing the height of the control button, changing it back, then selecting a cell on the sheet. Just resizing did not work consistently. Example below for a command button (cmdBALSCHED)

在 Excel 2016 中发现了同样的问题 - 能够通过更改控制按钮的高度、将其改回来，然后在工作表上选择一个单元格来纠正。只是调整大小并不一致。下面的命令按钮示例 (cmdBALSCHED)

Public Sub cmdBALSCHED_Click()

Sheet3.cmdBALSCHED.Height = 21
Sheet3.cmdBALSCHED.Height = 20
Sheet3.Range("D4").Select

This will reset the height back to 20 and the button font back to as found.

这会将高度重置为 20，并将按钮字体重置为找到的值。

回答by Michael Madigan

I experienced the same problem with ActiveX buttons and spins in Excel resizing and moving. This was a shared spreadsheet used on several different PC's laptops and screens. As it was shared I couldn't use macros to automatically reposition and resize in code.

我在 Excel 调整大小和移动中遇到了与 ActiveX 按钮和旋转相同的问题。这是在几台不同 PC 的笔记本电脑和屏幕上使用的共享电子表格。由于它是共享的，我无法使用宏在代码中自动重新定位和调整大小。

In the end after searching for a solution and trying every possible setting of buttons. I found that grouping the buttons solved the problem immediately. The controls, buttons, spinners all stay in place. I've tested this for a week and no problems. Just select the controls, right click and group - worked like magic.

最后在寻找解决方案并尝试所有可能的按钮设置之后。我发现将按钮分组立即解决了问题。控件、按钮、微调器都保持原位。我已经测试了一个星期，没有任何问题。只需选择控件，右键单击并分组 - 就像魔术一样工作。

回答by adamlane

After some frustrated fiddling, The following code helped me work around this Excel/VBA bug. Two key things to note that may help:

经过一些沮丧的摆弄之后，以下代码帮助我解决了这个 Excel/VBA 错误。需要注意的两个关键事项可能会有所帮助：

• Although others have recommended changing the size, and then immediately changing it back, notice that this code avoids changing it more than once on single toggle state change. If the value changes twice during one event state change (particularly if the second value is the same at the initial value), the alternate width and height properties may not ever be applied to the control, which will not reset the control width and height as it needs to be to prevent the width and height value from decreasing.
• I used hard-coded values for the width and height. This is not ideal, but I found this was the only way to prevent the control from shrinking after being clicked several times.

• 尽管其他人建议更改大小，然后立即将其更改回来，但请注意，此代码避免在单个切换状态更改时多次更改它。如果在一次事件状态更改期间值更改两次（特别是如果第二个值与初始值相同），则交替的宽度和高度属性可能永远不会应用于控件，这不会将控件宽度和高度重置为它需要防止宽度和高度值减小。
• 我对宽度和高度使用了硬编码值。这并不理想，但我发现这是防止控件在多次单击后缩小的唯一方法。

 
Private Sub ToggleButton1_Click()
   'Note: initial height is 133.8 and initial width was 41.4

    If ToggleButton1.Value = True Then
        '  [Code that I want to run when user clicks control and toggle state is true (not related to this issue)]
        'When toggle value is true, simply change the width and height values to a specific value other than their initial values. 

        ToggleButton1.Height = 40.4
        ToggleButton1.Width = 132.8
Else
        '  [Code that I want to run when user clicks control and toggle state false (not related to this issue)]
        'When toggle value is false adjust to an alternate width and height values. 
'These can be the same as the initial values, as long as they are in a separate conditional statement. 
         ToggleButton1.Height = 41.4
         ToggleButton1.Width = 133.8
    End If
End Sub

For a control that does not toggle, you may be able to use an iterator variable or some other method to ensure that the width and height properties alternate between two similar sets of values, which would produce an effect similar the toggle state changes that I used in this case.

对于不切换的控件，您可以使用迭代器变量或其他一些方法来确保宽度和高度属性在两组相似的值之间交替，这将产生类似于我使用的切换状态更改的效果在这种情况下。

回答by Hau

I had this problem using Excel 2013. Everything for working fine for a long time and all of sudden, when I clicked on the button (ActiveX), it got bigger and the font got smaller at the same time.

我在使用 Excel 2013 时遇到了这个问题。很长一段时间内一切正常，突然之间，当我单击按钮 (ActiveX) 时，它变大了，同时字体变小了。

Without saving the file, I restarted my computer and open the same Excel file again and everything is fine again.

在没有保存文件的情况下，我重新启动了计算机并再次打开同一个 Excel 文件，一切又正常了。

回答by Moz

Mine resized after printing and changing the zoom redrew the screen and fixed it

我的在打印和更改缩放后调整大小重新绘制屏幕并修复它

ActiveWindow.Zoom = 100
ActiveWindow.Zoom = 75

回答by SWa

Use a Forms button rather than an ActiveX one, ActiveX controls randomly misbehave themselves on sheets

使用 Forms 按钮而不是 ActiveX 按钮，ActiveX 控件在工作表上随机出现错误行为