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

Clavate

Why can't you lock the sheet? You should be able to use VBA to perform this task. Sheet.Protect/Sheet.Unprotect (there is an optional password variable you can pass as well) – Drayage

Problems isn't solved by locking as cause seems to be Windows not excel. See the answers to similar question here: https://mcmap.net/q/183002/-excel-the-incredible-shrinking-and-expanding-controls-closed/1047635 – Topless

Answer 1

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.

Candlewick

This is a good thought. My problem is that, as the clicking problem accumulates, I have to set Font.Size = 200 to see the text at all. I am not impressed by Microsoft, as this problem seems to have been existing for several years. – Shoemake

If I use an ActiveX control your code works. On the contrary, if I use a shape (for example, a circle, a square) and assign a macro to it, your code does not work. I did substitute CommandButton1 with the shape name (for example, rectangle2) but it does not work. Can you help me out? Thanks. – Colorcast

Answer 2

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

Lysin

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

Nomination

It looks like you forgot to format all of the code in your example. Starting with Sub Shared_ObjectReset() and ending with EndSub. – Dissipate

Best answer ever, solving all problems. Thanks for it, I tested and it instantly resized all my objects to the aspect I had defined manually. – Buhr

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.

Lydell

Answer 5

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

Tramline

Is he using an ActiveX button? I have seen this problem once before (ended up moving everything over to a new sheet, I believe) without using the ActiveX button. – Drayage

Answer 6

Do you have a selection command in the buttons macro?

Shortly after I renamed some cells in a worksheet including one that the toggle button selects after its toggle function, the font size shrunk. I fixed this by making sure Range("...").Select included the new cell name, not the coordinates.

Licha

Answer 7

It happens when the screen resolution / settings change after Excel has been open.

For example:

1. Open a workbook that has a button on it
2. Log in with Remote Desktop from a computer with different screen size
3. Click on the button => the button size will change

The only solution I found is to close Excel and reopen it with the new screen settings. All instances of Excel must be closed, including any invisible instance executed by other processes without interface must be killed.

Madra

In my case, although it is at least in part related to using screens with different resolutions, this issue does not need the resolution to be changed after Excel has been open. Excel 2007 on Windows 10, this issue did start when I started using an external monitor with my laptop, but I never switch monitor or change resolution anyway after opening Excel. External monitor plugged (and Windows set on "External screen only", so only external monitor used), turn on PC, open Excel, allow macros, click any button, it shrinks both in size and font. – Mosley

Answer 8

Old issue, but still seems to be an issue for those of us stuck on Excel 2007. Was having same issue on ActiveX Listbox Object and would expand its size on each re-calculate. The LinkCells property was looking to a dynamic (offset) range for its values. Restructuring so that it was looking to a normal range fixed my issue.

Duenna

Answer 9

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.

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

Mccafferty

Answer 10

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

ActiveWindow.Zoom = 100
ActiveWindow.Zoom = 75

Superimposed

Answer 11

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)

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.

Sola

Answer 12

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.

Swede

it didn't fix it for me – Parnell

Answer 13

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

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

Mound

Answer 14

This worked for me. I have a button called Today. I put these lines at the end of button's code. It basically sets the position and size of the button every time it is clicked.

'Reset button Today

'set the font size
Sheet4.Today.Font.Size = 16

'set the position
With ActiveSheet.Shapes("Today")
        .Top = ActiveWindow.Top + 15
        .Left = ActiveWindow.Left + 630
End With

'set the size of the button
Today.Height = 40
Today.Width = 250

Unpeopled