none
Excel Command Button Caption text shrinks till too small to read. RRS feed

  • Question

  • 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

    Wednesday, September 29, 2010 4:30 PM

Answers

  • 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
    • Marked as answer by Bessie Zhao Monday, October 11, 2010 10:45 AM
    Wednesday, September 29, 2010 6:15 PM

All replies

  • 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
    • Marked as answer by Bessie Zhao Monday, October 11, 2010 10:45 AM
    Wednesday, September 29, 2010 6:15 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
    Wednesday, September 29, 2010 9:10 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.
    Saturday, January 8, 2011 11:39 PM
  • 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....

    • Proposed as answer by excelvbaskier Sunday, December 23, 2012 4:40 AM
    • Unproposed as answer by excelvbaskier Sunday, December 23, 2012 4:42 AM
    Friday, December 21, 2012 11:29 PM
  • 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.
    • Proposed as answer by Erik Live Monday, January 7, 2013 12:36 PM
    Sunday, December 23, 2012 4:51 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

    Thursday, June 27, 2013 12:07 AM
  • 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 well
    Dim h As Integer    'command button height
    Dim w As Integer    '               width
    Dim 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 With
    End 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

    • Edited by zandvan Monday, August 12, 2013 4:31 PM
    • Proposed as answer by zandvan Monday, August 12, 2013 4:32 PM
    Monday, August 12, 2013 2:48 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, November 14, 2013 10:00 PM
  • This is very helpful.  Thanks for posting!
    Thursday, September 11, 2014 11:22 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, December 9, 2014 7:55 PM
  • Hey,

    developer mode - right click on the button - format control -> uncheck "lock aspect ratio".  Solved.

    br.

    vonTossis

    Tuesday, February 10, 2015 11:29 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".
    • Edited by dEX2018 Thursday, July 2, 2015 6:46 AM
    Thursday, July 2, 2015 6:15 AM
  • Confirmed this code works for me -- Excel 2007
    Monday, August 24, 2015 3:05 AM
  • 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
    Monday, August 31, 2015 11:02 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!

    Tuesday, February 9, 2016 4:30 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:02 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:10 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:25 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

    Thursday, February 11, 2016 6:39 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

    Wednesday, July 27, 2016 4:30 PM
  • 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.

    Thursday, March 9, 2017 12:20 AM
  • 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. 
    • Edited by jnnvac Wednesday, May 17, 2017 9:08 PM
    Wednesday, May 17, 2017 9:06 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.

    Thursday, July 6, 2017 4:18 PM
  • Is there anyway to get a sample workbook.  I am confused on how to call this up.  
    • Proposed as answer by Bricolux Wednesday, November 14, 2018 7:18 AM
    Friday, March 23, 2018 1:52 PM
  • 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?

    Wednesday, November 14, 2018 7:42 AM
  • 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
    



    • Edited by SimonTAtCCC Friday, January 25, 2019 4:06 PM
    Friday, January 25, 2019 2:33 PM