ActiveX Controls Resizing Bug

[adam_pallister]

Hello,

I'm having issues with a spreadsheet I've created. I've added in various Active X buttons/scroll bars/checkboxes and textboxes with macros linked to some.

When someone else uses the spreadsheet (it doesn't seem to happen on mine) when any of the controls are clicked strange things start happening i.e. the font size grows or the size of the control itself shrinks.

I can't use Form Control buttons as I need the extra functionality of the ActiveX ones and I've tried changing the Object Positioning in the Properties tab under Format Control to all 3 Move and Size settings without any sucess.

If anyone knows the answer or even if it's impossible to correct that would be really helpful.

Thanks
Adam

 

[cannon_fodder]

Hello,

I'm having issues with a spreadsheet I've created. I've added in various Active X buttons/scroll bars/checkboxes and textboxes with macros linked to some.

When someone else uses the spreadsheet (it doesn't seem to happen on mine) when any of the controls are clicked strange things start happening i.e. the font size grows or the size of the control itself shrinks.

I can't use Form Control buttons as I need the extra functionality of the ActiveX ones and I've tried changing the Object Positioning in the Properties tab under Format Control to all 3 Move and Size settings without any sucess.

If anyone knows the answer or even if it's impossible to correct that would be really helpful.

Thanks
Adam

I'm interested, too. Unfortunately, I can produce this with multiple spreadsheets including one that used to work pretty good. I was using Excel 2003 but now I'm using 2007 and it is much worse. Before, I used to have an SS with a mixture of ActiveX and Forms controls. This new SS I decided to only use ActiveX.

Here is what happens. Upon leaving design mode and start testing it, every time I click on a checkbox, the font grows larger. After a few times you can't even see the text it has gotten so big. This is ridiculous!

The text grows with the Option buttons, too.

 

[cannon_fodder]

Hello,

I'm having issues with a spreadsheet I've created. I've added in various Active X buttons/scroll bars/checkboxes and textboxes with macros linked to some.

When someone else uses the spreadsheet (it doesn't seem to happen on mine) when any of the controls are clicked strange things start happening i.e. the font size grows or the size of the control itself shrinks.

I can't use Form Control buttons as I need the extra functionality of the ActiveX ones and I've tried changing the Object Positioning in the Properties tab under Format Control to all 3 Move and Size settings without any sucess.

If anyone knows the answer or even if it's impossible to correct that would be really helpful.

Thanks
Adam

Adam,

I received no response here yet (which is where I always come first since the support is fantastic) so I kept searching and I found an answer that easily solves my problem.

1. Shut down Excel
2. look in \Documents and Settings\<username>\Application Data\Microsoft\Excel and delete a file starting with Excel11 (or rename it with a .bak extension if you want to keep it for sentimental value).
3. Restart Excel

My problem immediately went away.

 

[achin1]

wow this actually worked for me and the file i deleted started with an Excel12... odd

 

[eircmoreland]

I have the issue, but it did not appear until we migrated to Windows 7. It also seems to be linked to undocking a laptop or plugging into a projector - events that force automatic display aspect ratio and resolution changes. Once it starts, it gets worse when switching to and from developer mode. The font resizing of the list boxes is the worst. I've tried the fix (deleting or renaming the Excell11 or Excell2 files) and it did not work. This is a real problem for us.

 

[Michal Garbers]

Here is the code I am using to fix this issue

Sub ButtonReset()

Dim ButtonCount As Integer
Dim MyShapes As OLEObjects
Dim ButtonSelected As OLEObject


'OLE Programmatic Identifiers for Commandbuttons = Forms.CommandButton.1
Set MyShapes = ActiveSheet.OLEObjects
For Each ButtonSelected In MyShapes
'Remove this line if fixing active object other than buttons
If ButtonSelected.progID = "Forms.CommandButton.1" Then
ButtonSelected_Height = ButtonSelected.Height
ButtonSelected_Top = ButtonSelected.Top
ButtonSelected_Left = ButtonSelected.Left
ButtonSelected_Width = ButtonSelected.Width


ButtonSelected.Placement = 3

ButtonSelected.Left = ButtonSelected.Left + 1
ButtonSelected.Top = ButtonSelected.Top + 1
ButtonSelected.Left = 10
ButtonSelected.Width = 10


ButtonSelected.Height = ButtonSelected_Height
ButtonSelected.Top = ButtonSelected_Top
ButtonSelected.Left = ButtonSelected_Left
ButtonSelected.Width = ButtonSelected_Width


End If
Next
End Sub

 

[ahp549]

great! Do you have a similar fix routine for checkboxes and radiobuttons?

 

[Joshua_JC]

Hi Everyone,

Its not very often that I feel like i know something that might help someone but I actually ran into this issue today, and found another possible fix.

1) Enter "Design Mode"
2) Right click the checkbox
3) Select "Properties"
4) Change the 5th option "AutoSize" to "True"
5) Close the menu and turn off Design Mode

The check box will change as you update them (if you have more then one) and the size stays the same even if clicked 50 times (i checked lol)

Hope you don't mind the additional info in your thread.

Joshua

 

[engineer69]

I have the same issue. If I use an option button as an Active X, then sometimes I have this issue, however by changing to a "Form" version of the option box, it never happens. This clearly is an Active X issue from Microsoft's end and doubt there's an easy fix that always works.

 

[SantiBailors]

I found a treasure trove of additional explanations and solutions for this bug in this thread at Stack Overflow. It's really incredible that this bug is still alive and kicking after so many years. Unbelievable, and yet normal. Whatever, we are all used to this and those who aren't will be.

For the record:

- I solved this problem by setting AutoResize to true as per Joshua_JC's suggestion above (thanks heaps, Joshua_JC). But in other cases I won't be allowed to give up control on the controls' size so I will probably have to try one of the many other solutions (so, thanks to everybody who contributed info).

- My system is Excel 2007 on Windows 10, with an HP laptop. My problem started only when I started using an external HDMI monitor.