Printable View
Has anyone experienced their buttons on an excel sheet shrinking when you click on them ?
It happens to me sometimes. Or the text inside the control (eg a combobox) gets bigger each time I use it.
Doesn't always happen and not always on the same sheet either.
Any one any clue as to what the .... is going on with this?!
No idea.
Does this happen on all your spreadsheets or just one of them?
Which version of Office Excel are you using 2003 or 2007?
2003. I work through a remote connection to a server that all my company works off so I don't know if that has anything to do with it or not compared to a standalone version
Are you able to attach a screen shot of it looks like? This will help me and others better understand the problem.
There is no solution for this at the moment. It is a bug. same problem here...
http://www.vbforums.com/showthread.php?t=552717
This is a very odd problem in deed. I am a very heavy excel user and this problem has recently waisted tons of my time. Has anybody come across any new improvements in this bug? It happens intermittently and I can't seem to place a finger on why. I have re-added all my comboboxes adjusted the zoom property etc..etc...etc...etc and nothing. I have noticed that it seems to be unique to comboboxes directly on a spreadsheet. It doesn't seem to effect anything on a vba form.
Anyway, if anybody has anything new...do tell.
Yes. I found I was getting this problem when moving to a different computer with a different resolution.
I'd open my remote desktop on one computer and open my Excel sheet. Then, without closing Excel on my session, I'd move to a different computer with a different screen resolution and jump into my session there. It was then that I would start seeing all my controls changing size when clicking on them. The solution was to not do that ! Or save and close ALL of Excel before moving computer....
Thanks for the input. I will test your theory on my system. I use multiple monitors when i am working with Excel and VBA and they do run in different resolutions. I will give it a try.
This is a bug in Excel 2003. According to Microsoft, it was fixed with Service Pack 1.
Read this:
Controls on an Excel 2003 worksheet change size after you change the screen resolution
That also provides a hotfix.
Thanks! That did the trick. What an odd problem.
I still have this problem. Here is the fix I use:
Sub Reset_Buttons()
'
' Kluge to reset button and font size to resolve shrinking/enlarging buttons
' Bisham Singh 2011 12 23
' This is sledge hammmer approach. If there is a better one, please let us know.
' I run this everytime a button is triggered.
Dim mShape As Shape
Dim mWorkSheet As Worksheet
Dim mOLE As OLEObject
For Each mWorkSheet In ThisWorkbook.Sheets
For Each mOLE In mWorkSheet.OLEObjects
mOLE.Width = 74
Next
For Each mOLE In mWorkSheet.OLEObjects
mOLE.Width = 75
mOLE.Height = 35
mOLE.Object.FontSize = 10
mOLE.Object.FontBold = False
Next
Next
End Sub
Geezus - this issue has been killing me for months!
Tried EVERYTHING listed above (and more!) - nothing worked...
UNTIL NOW!
I just found the solution on another site and wanted to share it here. I take NO credit for this! If it works for you - then AWESOME - but don't thank me... I'm just passing it along.
OK - here ya go:
i) In design view, select all the controls in the sheet.
ii) Right click and select Grouping ->Group to group the controls
Then save the workbook.
By golly THAT did the trick for me!
Good luck!
- RJ
Bingo! Love the group option. This bug has driven me nuts.
Same thing, the buttons shrinks.
On one sheet I have a mix of control and if I group them, I get an error about a connection which name cannot be found in the project's code while I know it was once there.
I also have the problem with charts legends that shrinks overtime.
Go to Developer Tab, Design Mode.
Then right click your button and select Format Control.
Select properties and select don't move or size. :)
Thab