excel - VBA Excel 按钮单击后会调整大小(命令按钮)

Question

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

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

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

Answer 1

我将以下内容用于列表框。按钮的原理相同；酌情调整。

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

这使用以下类型和过程:

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