Macro buttons resize randomly

Question

The macro buttons in my workbook resize themselves (seemingly randomly). The macro buttons are on several sheets near the top of each sheet.  They display text (such as "Show Top 10") and when they resize, they shrink so that not all the text is displayed.  However, the buttons still work to run the macros. The workbook was created years ago, and I have converted it to the current version of Excel whenever we update (currently 2010).  We update the data in the workbook weekly and this began happening about a month ago.  I share the workbook with 2 coworkers, and we are all on the same Excel version. We all see the same thing.  We all have macro settings set to "enable all macros" and ActiveX settings set to "Prompt me before enabling all controls with minimal restrictions".

Any ideas on what causes this and how we can prevent the buttons from resizing?

Answer 1

Right-click on the controls and select "Format Control..." Navigate to the "Properties" tab. Make sure that the option "Don't move or size with cells" is selected.

Kevin

Answer 2

This is a common problem with ActiveX controls. If you can use the simpler Forms controls then please do. ActiveX controls not only misbehave as you have observed, they can cause workbook corruption.

Kevin

Answer 3

Hi,

try this to solve your problem,

select one or more cells and run this code..

Sub AddNewButtons()

On Error Resume Next

Dim rng As Range, r As Range

Set rng = Selection

For Each r In rng

ActiveSheet.Buttons.Add(r.Left, r.Top, r.Width, r.Height).Select

r.Placement = xlMoveAndSize

Next

rng.Select

End Sub

next,

right click on button for edit....

Answer 4

I just checked and the macro buttons are form controls, not ActiveX controls.