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

Question

How can I stop button resizing? Each time I press a button, either the button size or the font size changes.

Note. I cannot lock my sheet, as my macro will write to the sheet.

Startup is disabled. I am running Excel 2007 on Windows 7 (64 bit).

Answer 1

I use the following for ListBoxes. The same principle for buttons; adapt if necessary.

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 

The following types and procedures are used here:

 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 the code to the end of onClick this way:

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

Seems to work.

My problem was slightly different. Having opened the book on my main laptop display, move it to my large monitor. I would suggest the same reason.

Answer 3

See this problem in Excel 2007, 2010, and 2013.

This code prevents the problem from occurring. The code should run every time an active X is active.

 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 that there are two different questions: - changing the size of the ONE button when you click on it (although I don’t always know why), and - changing the size of the ALL buttons when the book is opened on the display with a different resolution (which remains even when you return to the original screen).

Regarding a separate problem with resizing: I found that it is enough to change one button size to rejuvenate it. For example:

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

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

Answer 5

Use the Forms button, not ActiveX; ActiveX controls accidentally misbehave on sheets

Answer 6

Do you have a select command in a button macro?

Shortly after I renamed some cells on the sheet, including the one that the switch button selects after its switch function, the font size was reduced. I fixed this by making sure Range ("..."). The number includes the new cell name, not the coordinate.

Answer 7

This happens when the resolution / screen settings change after opening Excel.

For example:

• Open the book that has the button.
• Remote desktop login from a computer with different screen sizes
• Press the button => the button size will change

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

Answer 8

An old question, but it still seems to be a problem for those of us who are stuck in Excel 2007. I had the same problem in the ActiveX Listbox Object and expanded its size with each recount. The LinkCells property looked for a dynamic range (offset) for its values. Restructuring, so she was looking for a normal range, fixed my problem.

Answer 9

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

Without saving the file, I restarted my computer and opened the same Excel file again, and everything will be fine.

Answer 10

My resizing after printing and zooming changed the image and fixed it

 ActiveWindow.Zoom = 100 ActiveWindow.Zoom = 75 

Answer 11

I found the same problem with Excel 2016 - I managed to fix it by changing the height of the control button, returning it back, then selecting a cell on the sheet. Just resizing doesn't work sequentially. Example below for command button (cmdBALSCHED)

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

This will return the height back to 20, and the button font will return to the original.

Answer 12

I had the same problem with Excel ActiveX buttons and rotations when resizing and moving. It was a common spreadsheet used on several different laptops and PC screens. Since this was common, I could not use macros to automatically reposition and resize in code.

In the end, after finding a solution and trying all kinds of button settings. I found that grouping the buttons immediately solved the problem. Controls, buttons, rotators remain in place. I checked this for a week and no problem. Just select the controls, right-click and group - it works like magic.

Answer 13

After some frustration, the following code helped me get around this Excel / VBA error. Pay attention to two key things:

• Although others have recommended resizing and then immediately resizing it immediately, note that this code does not allow you to resize it more than once when changing one switching state. If the value changes twice during one change in the state of the event (especially if the second value matches the initial value), the alternative width and height properties may never be applied to the control, which will not reset the width and height of the control as it should for prevent reduction in width and height.
• I used hard-coded width and height values. This is not ideal, but I found that this was the only way to prevent the control from being compressed after a few taps.

 
 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 doesn't switch, you can use an iterator variable or some other method to ensure that the width and height properties alternate between two identical sets of values, resulting in an effect similar to the switching state changes I used in this case.