Activex Controls Changing in Size

[mcarter01]

I am using the command button, option button and checkbox activex controls in a workbook. I have set the font type and size in the properties, set the format control lock aspect ratio on and object positioning to Don't move or size with cells.

With this, the activex controls will randomly get smaller or bigger each time you click on them for the action to occur. The action in the code occurs fine, but the text becomes unreadable either because it is very small or too large to see in the size of the control space. When I switch to design mode and resize the control the text returns to the defined size, but this is not a clear option for usability. Any suggestions on how to resolve?

Thanks!

 

[GlennUK]

I am having the exact same issue with a combobox and listbox.

What issue? That they are changing size? Are you using a machine via remote access? What version of Excel? What SPs have been applied?

 

[rconverse]

Yes, size issue. Not remote access. Using 2007, not sure about SP's.

 

[RoryA]

It's a long standing issue with ActiveX controls on worksheets in Excel. Particularly common if you use Zoom settings other than 100% but that's not the only cause. If you can. use Forms controls instead. If not, have a read of this article. (I think it's outside the paywall)

 

[judas]

This is a long standing issue related to ActiveX controls and changing resolutions while having the workbook open (hence the problems with remote desktop, when connecting to external display and, in my particular case, maximizing virtualized Windows windows).

It is discussed in-depth here:

Command Buttons resizing themselves without changing size of cells around

The fixes proposed include:

1. Group Controls. (This did it for me)
2. Add code to always change font size & button size
3. Download hotfixes

I hope this gives some light to those of you having this same problem...it surely was frustrating!

 

[aviaf]

For anyone wishing to download the hotfixes mentioned above, see here: ActiveX and form controls resize themselves when clicked or doing a print preview - The Microsoft Excel Support Team Blog - Site Home - TechNet Blogs

 

[rproudfit]

I am having the exact same issue with a combobox and listbox.

The problem is that ActiveX buttons have an instability issue that Form buttons do not have. Do the following to fix:

1. Create a form button
2. Move the code from the sheet (i.e., Sheet1 (Example) to a module
3. Assign the macro to the form button

If you changed the color of the ActiveX button and want the new one to match, do the following:

1. Find the hex BackColor property of the ActiveX button
2. Go to https://officetricks.com/convert-excel-vba-color-codes-rgb-hex/ to convert the hex code to RGB
3. Insert a shape
4. Change the shape fill color to match

Thank you,
Ryan