Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
Question
Wednesday, September 29, 2010 4:30 PM
Hi All,
The caption text in some of the command buttons on this spreadsheet keeps getting smaller, a little at a time as the buttons are clicked, until it is unreadable. They are (supposed to be) using a size 8 Ariel font.
The property sheet for the buttons always shows the size the font should be, what it started at, like 8; but the size as viewed on the screen is 3 or 4 and, eventually, even smaller.
CommandButtons’ response to attempts to fix the problem have been mixed.
I was trying to "remind" one button of what size it was supposed to use on screen by adding a line in that button’s click event to make the font size the same as showed in the property sheet, and eventually bigger, when that did not work. When I got the button.Font.Size command up to size 30, the caption finally showed up at about a size 8 on screen, while now showing size 30 in the property sheet.
A different command button changed its font and its property sheet both to 30 in response to the added VBA command in its click event. I returned that one to 8, and it is either doing ok or is changing slowly, again. While troubleshooting it, I did add one command, a Application.ScreenUpdating=True line was added to the button’s macro. I am not sure if the ScreenUpdating command had any effect, but that was when I noticed that the tiny fuzzy dot that that caption had originally become had actually disappeared, instead of just getting smaller. I dragged that button larger in design mode and there was the size 30 caption. I changed that one back to 8 in the click event, and either it is doing ok, so far, or the font is getting smaller very gradually.
I went back to the first button, which was showing 8 now, even though it had 30 for font size in the property box. After making sure it had a screen updating command, I also tried changing the font style (Ariel to Times) to see if that would ‘remind’ it what size to show. That did not help. It is back to showing a 3 font while an 8 shows on the property sheet and in the extra line in the click event. I returned it to 30 in the click event and it is now 30 there and on the property sheet, and about size 8 on screen.
There are about 30 buttons and about 11 of them have this problem. These may just be the only buttons currently in use. Most of these buttons link to some pretty sophisticated macros in VBA.
I have tried screen updating, application.calculating, changing font and font size. Nothing is reliable, or should be necessary, really. It is probably something really simple, I just can’t think of it.
Does anyone know the solution?
Thanks,
SongBear
All replies (27)
Wednesday, September 29, 2010 6:15 PM ✅Answered
Hi SongBear,
The caption text in some of the command buttons on this spreadsheet
keeps getting smaller, a little at a time as the buttons are clicked,
until it is unreadable.
Do forms buttons behave themselves? (I prefer the fomrs controls over the ActiveX ones just for this reason: they are less problematic).
Have you got a zoom percentage other than 100% on the sheet?
Regards, Jan Karel Pieterse|Excel MVP|http://www.jkp-ads.com
Wednesday, September 29, 2010 9:10 PM
What version of Excel are you in?If this answer solves your problem, please check Mark as Answered. If this answer helps, please click the Vote as Helpful button. Cheers, Shane Devenshire
Saturday, January 8, 2011 11:39 PM
Did you find a solution to this issue? I have the same problem and I have tried everything I could find on the topic. I have a sheet that is normally viewed at 75% zoom. If I leave the sheet at 100% then I do not see these issues.
Friday, December 21, 2012 11:29 PM | 3 votes
It's a couple of years on but I came to this thread looking for an answer to the same issue. If anyone is still wondering how to overcome it I have done this (which may not be very elegant but at least works). Insert these lines at the end of each button's code:
CommandButton5.AutoSize = False
CommandButton5.AutoSize = True
CommandButton5.Height = 23.25
CommandButton5.Left = 1680
CommandButton5.Width = 100
This was the particular code for my 'commandbutton5'. You should replace this with the appropriate name of your button and also replace the dimensions and location of the button with your appropriate figures which are obtainable from 'properties'.
Works a treat, though it shouldn't be necessary! Just 110 buttons to go....
Sunday, December 23, 2012 4:51 AM | 2 votes
Try going to C:\Users\yourusername>\AppData\Roaming\Microsoft\Excel (AppData may be a hidden folder). Delete any files starting in XL<hexnumber> and ending in .xlsb or xlb. These files are created when you use two monitors of different resolutions or use a projector and save the file under a different resolution. Deleting them solved my font shrinking problem. I also make sure not to close the workbook with the activex controls as the current page because if reopened the workbook on a different resolution monitor they would be off if the activex controls were on the opening page, but ok if the opening page had no activex controls.
Thursday, June 27, 2013 12:07 AM
Hi all,
I know this is an old question, but I recently solved it for my own large worksheet.
My toggle buttons were resizing after I click anywhere on the sheet - they were toggling the hidden state of a textbox. After End If I made it select a1 so that it's not necessary to click away from the toggle button to continue with the sheet.
I had just renamed A1 in some sheets including this one with the problematic toggle and then the font shrinking started. Does your command button macro have anything like this cell slection involved? If so, make sure that the Range("...") contains the name of the cell, not the coordinates.
Thanks
Zsombor
Monday, August 12, 2013 2:48 PM | 9 votes
I tried several methods to address the issue of command button text shrinking as well as command button size increasing (and combinations of the two), none of which solved both problems. The way my workbook was used meant that I was not able to control zoom level or screen resolution.
A further issue is that I wanted a solution which would be easy to leverage in existing or future workbooks so after a couple of dozen quick tests, here is my solution. In a common module, I place the following:
Public Sub ResetButton(ByRef btn As Object)' Purpose: Reset button size and font size for form command button on worksheet' Addresses known Excel bug(s) which alters button size and/or apparent font size' Parameters: Reference to button object' Remarks: Getting/setting font size fails since font size remains the same; display (apparent) size changes' AutoSize maximizes the font size to fit the current button size in case it has changed' Button size is reset in case it has changed' Finally, font size is reset to adjust for font changes applied by AutoSize' This fix seems to handle shrinking button icon sizes as wellDim h As Integer 'command button heightDim w As Integer ' widthDim fs As Integer ' font size With btn h = .Height 'capture original values w = .Width fs = .Font.Size .AutoSize = True 'apply maximum font size to fit button .AutoSize = False .Height = h 'reset original button and font sizes .Width = w .Font.Size = fs End WithEnd Sub
In each "_click" event, I just add a call to this routine, for example, at the top of cmdRefresh_click, I add:
ResetButton cmdRefresh
Hope that helps someone else reduce the amount of code required to work around these issues.
Cheers,
Z
Thursday, November 14, 2013 10:00 PM
Hi Excelbaskier
This also worked for me. I am using Windows 8.1 and was having the same problem. I was getting a smaller font size for some of my button Captions. In addition, some of my code for the buttons was causing an error and the code stopped. Once I added your code to the end of the code in my problem buttons, everything started working without any errors. I even changed the lines of one button to comments just to see if the buttons would still behave properly and that the font didn't get small again. This worked. My problems started when I got a new PC and reinstalled my Office 2007. With the new Windows 8 and 8.1 I was doing a lot of experimenting with the System Personalize - Display - Make all items lareger. At one point I used Zoom of 200. This did not work well with other programs. I finally stablized at 150 and everything seems more stable. My suspicion is (and as noted by others concerning zoom) is that fooling with the Zoom can be troublesome
Thanks for your fix.
Bill Watt
bill w
Thursday, September 11, 2014 11:22 PM
This is very helpful. Thanks for posting!
Tuesday, December 9, 2014 7:55 PM
Ahhh i tried this and the button disappeared entirely. beware if you try, save your worksheet first so you can close out and abandon changes.
Tuesday, February 10, 2015 11:29 AM | 1 vote
Hey,
developer mode - right click on the button - format control -> uncheck "lock aspect ratio". Solved.
br.
vonTossis
Thursday, July 2, 2015 6:15 AM
Many thanks vonTossis! But I'm a bit puzzled. Had the issue with a button where the font size became so small that text in button was unreadable. When checking "lock aspect ratio" font size was back to normal. So now I'm not sure if I need to check or uncheck (like you mention) "lock aspect ratio".
Monday, August 24, 2015 3:05 AM
Confirmed this code works for me -- Excel 2007
Monday, August 31, 2015 11:02 PM | 2 votes
I've run into this issue only when the TakeFocusOnClick property of the command button is set to False. Here's my simple workaround:
Private Sub SomeBtn_Click()
'Do stuff...
'***EXCEL BUG WORKAROUND*************************************************************************
'When a command button's TakeFocusOnClick property is set to False, as it is for this button, the
'displayed size of its caption text can sometimes shrink or grow with each button-click even
'though the font size is not actually changed (i.e. similar to a zoom operation on the caption
'text only). A workaround is to just resize the button height to a different size and then back
'to its original size, when the button is clicked:
With SomeBtn
Dim BtnHeight As Single
BtnHeight = .Height
.Height = BtnHeight + 1!
.Height = BtnHeight 'Can't just decrement since resolution is 15 twips (15/20 = 0.75
End With 'of a point).
'***END WORKAROUND*******************************************************************************
End Sub
Tuesday, February 9, 2016 4:30 PM
WHilst this is a couple of years old, you'll be pleased to know that this solution still works perfectly - great solution Zandvan. Helped me out!
Thanks!
Thursday, February 11, 2016 6:02 PM
i have tried your solution several times.. here is the code i have and i tried inserting your fix at the end..
continues to come up with "compile error, invalid qaulifier"
do you think you can spot what i am missing here?
Private Sub Scenario1_Click()
Dim Scenario1 As String
Scenario1 = InputBox("Please enter a scenario name", "Save", zwischenrechnung.Range("B6").Value)
If Scenario1 = "" Then Exit Sub
scenario.Range("Eingabe").Copy
zwischenrechnung.Range("B8").PasteSpecial Paste:=xlPasteValues
zwischenrechnung.Range("B6") = Scenario1
scenario.Range("scenario").Copy
zwischenrechnung.Range("B23").PasteSpecial Paste:=xlPasteValues
scenario.Range("C8").Select
Application.CutCopyMode = False
End Sub
Thursday, February 11, 2016 6:10 PM
here is what i have, i tried your code does not solve the issue..when i disconnect my pc from my desktop monitor and walk to a meeting with my laptop and then re-open the screen, the macro buttons get larger and larger and larger..
here is my code:
rivate Sub Scenario1_Click()
Dim Scenario1 As String
Scenario1 = InputBox("Please enter a scenario name", "Save", zwischenrechnung.Range("B6").Value)
If Scenario1 = "" Then Exit Sub
scenario.Range("Eingabe").Copy
zwischenrechnung.Range("B8").PasteSpecial Paste:=xlPasteValues
zwischenrechnung.Range("B6") = Scenario1
scenario.Range("scenario").Copy
zwischenrechnung.Range("B23").PasteSpecial Paste:=xlPasteValues
scenario.Range("C8").Select
Application.CutCopyMode = False
End Sub
Thursday, February 11, 2016 6:25 PM
i get hung up on mine because i think it is a private sub and string..i tried entering your code at the bottom of mine and it does not work..
here is my code..
Private Sub Scenario1_Click()
Dim Scenario1 As String
Scenario1 = InputBox("Please enter a scenario name", "Save", zwischenrechnung.Range("B6").Value)
If Scenario1 = "" Then Exit Sub
scenario.Range("Eingabe").Copy
zwischenrechnung.Range("B8").PasteSpecial Paste:=xlPasteValues
zwischenrechnung.Range("B6") = Scenario1
scenario.Range("scenario").Copy
zwischenrechnung.Range("B23").PasteSpecial Paste:=xlPasteValues
scenario.Range("C8").Select
Application.CutCopyMode = False
End Sub
Thursday, February 11, 2016 6:39 PM
mine is using the forms button and i still have the same issue that when i disconnect and go to a meeting and present on another screen the macro buttong grows and grows and grows..
here is my code..
Private Sub Scenario1_Click()
Dim Scenario1 As String
Scenario1 = InputBox("Please enter a scenario name", "Save", zwischenrechnung.Range("B6").Value)
If Scenario1 = "" Then Exit Sub
scenario.Range("Eingabe").Copy
zwischenrechnung.Range("B8").PasteSpecial Paste:=xlPasteValues
zwischenrechnung.Range("B6") = Scenario1
scenario.Range("scenario").Copy
zwischenrechnung.Range("B23").PasteSpecial Paste:=xlPasteValues
scenario.Range("C8").Select
Application.CutCopyMode = False
End Sub
Wednesday, July 27, 2016 4:30 PM
Seen this issue in Excel 2007, 2010 and 2013.
Here is the code which prevent the issue from manifesting. Code needs to run every time a active x object is activated.
Sub Shared_ObjectReset()
Dim MyShapes As OLEObjects
Dim ObjectSelected As OLEObject
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_Font = ObjectSelected_FontSize + 1
ObjectSelected.Height = ObjectSelected_Height
ObjectSelected.Top = ObjectSelected_Top
ObjectSelected.Left = ObjectSelected_Left
ObjectSelected.Width = ObjectSelected_Width
ObjectSelected_Font = ObjectSelected_FontSize
End If
Next
End Sub
Thursday, March 9, 2017 12:20 AM
Closing Excel and re-opening Excel it made this issue disappear for me.
Seems to be related to screen resolution settings being change after Excel has been open.
Wednesday, May 17, 2017 9:06 PM
Thanks excelvbaskier, this worked for me. I kept having this issue every time i undocked my laptop and had the file open. For me it would actually increase the size of the ActiveX Text Box. i did the above and closed all instances of Excel reopened the file and recreated the ActiveX Text Box and works like it should.
Thursday, July 6, 2017 4:18 PM
Thank you for this bit of code. My own spreadsheet started shrinking my text after having worked with the spreadsheet for several months without issue.
Just one note though. I tried calling the routine at the top of my code (i.e. before button actions) as your notes suggested and the text still shrank. Did I miss interpret "for example, at the top of cmdRefresh_click"?
When, I then called the routine at the bottom of my button action code, I found the code worked as I had hoped it would.
Friday, March 23, 2018 1:52 PM
Is there anyway to get a sample workbook. I am confused on how to call this up.
Wednesday, November 14, 2018 7:42 AM
I've encountered the same problem. Notice that this issue is known since 2010..... and Microsoft has not released a fix. Microsoft just does not care about bugs and prefer to redesign the user interface, shuffling the menus around at each new release. I would say that there has been no progress and only more bugs added since 0ffice 95.
Zero support from Microsoft!!!! Even Open Office does a better job at fixing bugs. Should we really continue paying licences to Microsoft?
Friday, January 25, 2019 2:33 PM
My variant :
Class Module & via ThisWorkbook events :-
Enter the code below into a Class Module called "cls_CommandButtonFontDisplayFix" ; then follow the instructions (in the Comment) about deploying into ThisWorkbook events. once setup you can copy/import the class module where it's required and only have to add the ThisWorkbook event code (v.few lines) per workbook.
Code follows ::==
Option Explicit
' cls_CommandButtonFontDisplayFix
' v1.01 - with GroupObject recursion
' To fix problem of Command (ActiveX) button's font display size reducing (on click) , which especially occurs on setups with multiple monitors / odd resolutions
' TO APPLY
' IMPORT / ADD this Class Module
' In ThisWorkbook (Microsoft Excel Object in Project Explorer) :-
' Private oCBFDF_wbk As New cls_CommandButtonFontDisplayFix
'
' Private Sub Workbook_Open()
' Workbook_SheetActivate ActiveSheet
' End Sub
'
' Private Sub Workbook_SheetActivate(ByVal Sh As Object)
' oCBFDF_wbk.Initialize4Sheet Sh
' End Sub
' Comment
'=========
' You could try and avoid the re-load collection of events each time a sheet is activated but that wouldn't work so well during development
Private collCBevents As Collection
Public WithEvents AnyCmdButton As MSForms.CommandButton
Private Sub AnyCmdButton_Click()
CmdButtonFontDisplayFix AnyCmdButton
End Sub
Private Sub CmdButtonFontDisplayFix(ByRef cmdButton As CommandButton)
Dim vSU As Variant
vSU = Application.ScreenUpdating
On Error GoTo CmdButtonFontDisplayFix_exit
If Application.ScreenUpdating Then Application.ScreenUpdating = False
With cmdButton
.Height = .Height + 1
.Height = .Height - 1
End With
CmdButtonFontDisplayFix_exit:
If Not Application.ScreenUpdating = vSU Then Application.ScreenUpdating = vSU
End Sub
Public Sub Initialize4Sheet(ByRef wsht As Worksheet)
Dim shp As Shape
Dim CBFDF As cls_CommandButtonFontDisplayFix
Dim vSU As Variant
vSU = Application.ScreenUpdating
On Error GoTo Initialize4Sheet_error
If wsht.Shapes.Count > 0 Then
Set collCBevents = New Collection
For Each shp In wsht.Shapes
If TypeName(shp.OLEFormat.Object) = "GroupObject" Then
recurseGroupObjects shp.OLEFormat.Object
ElseIf TypeName(shp.OLEFormat.Object) = "OLEObject" Then
If TypeName(shp.OLEFormat.Object.Object) = "CommandButton" Then
setup4CmdButton shp.OLEFormat.Object.Object
End If
End If
Next
End If
Initialize4Sheet_exit:
If Not Application.ScreenUpdating = vSU Then Application.ScreenUpdating = vSU
Exit Sub
Initialize4Sheet_error:
With Err
MsgBox "Error(" & .Number & ") - " & .Description, vbCritical + vbOKOnly, "CommandButtonFontDisplayFix Init.", .HelpFile, .HelpContext
End With
Resume Next
End Sub
Public Sub recurseGroupObjects(ByRef oGrp As GroupObject)
Dim oShp As Shape
For Each oShp In oGrp.ShapeRange.GroupItems
If TypeName(oShp.OLEFormat.Object) = "GroupObject" Then
recurseGroupObjects oShp
ElseIf TypeName(oShp.OLEFormat.Object) = "OLEObject" Then
If TypeName(oShp.OLEFormat.Object.Object) = "CommandButton" Then
setup4CmdButton oShp.OLEFormat.Object.Object
End If
End If
Next
End Sub
Public Sub setup4CmdButton(ByRef oCB As CommandButton)
Dim CBFDF As cls_CommandButtonFontDisplayFix
Set CBFDF = New cls_CommandButtonFontDisplayFix
Set CBFDF.AnyCmdButton = oCB
collCBevents.Add CBFDF
CmdButtonFontDisplayFix oCB
setup4CmdButton_exit:
Exit Sub
setup4CmdButton_error:
With Err
MsgBox "Error(" & .Number & ") - " & .Description, vbCritical + vbOKOnly, "CommandButtonFontDisplayFix setup4CmdButton.", .HelpFile, .HelpContext
End With
Resume Next
End Sub
Wednesday, September 18, 2019 9:06 AM
My issue is that the font when the button is not selected, distorts to bold and wide.
I created a sheet on another machine - looks fine.
Copy that sheet to the offending machine - it has the same problem as the file on that machine which has the issue. So it appears to be a machine issue rather than a file content issue.
I've removed all the files under Appdata by renaming to *_old.* but no luck. I do think though that this reply of Eric live has an element of truth, but where is the offending setting file? tried removing word files too but nothing.
Done some more fiddling. The other machine is a Amazon workspace. I'm working on a 3K monitor, but if I scale the window to something smaller than 1920x1080 the problem goes away.