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

Question

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

Answer 1

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

Answer 2

I added some code to the end of the onClick thus:

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.

Answer 3

Seen this issue in Excel 2007, 2010 and 2013

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

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

Answer 4

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

Answer 5

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.

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.