none
Command Buttons resizing themselves without changing size of cells around

    Question

  • I've created a spreadsheet in Excel 2010 with commandbuttons and comboboxes.

    One of the user noticed that on Excel 200, some objects resize themselves for no apparent reason. They get bigger and there has been NO re-sizing of the cells/columns/rows around the buttons. Just using them seems to trigger the resizing.

    I cannot recreate the problem on Excel 2010 nor on Excel 2003.

    Has anybody got any idea of what is going on?

    thanks in advance

     

    Tuesday, October 12, 2010 10:46 AM

Answers

  • Caroline,

    Thanks for the information.

    No, I cannot reproduce it at all.

    I don't have any immediately fix to the problem but what You can do is to make sure that each control has the setting "Don't move and size with cells" selected and that the option "Print Object" is checked. To do so You right click on each control and from the pop up menu select "Format control..." and then select the tab "Properties".

    A workaround is to add a VBA procedure that resize all the controls to the wanted sizes and can be executed when this happens.



    Kind regards, Dennis (http://xldennis.wordpress.com/)
    • Marked as answer by Bessie Zhao Tuesday, October 19, 2010 9:27 AM
    Tuesday, October 12, 2010 4:25 PM

All replies

  • Caroline,

    Could You have the kindness to give us some more details:

    Which version of Excel 2010 is in use

    Which type of Command buttons is in use

    Which file format the files are saved in.

    It seems that there exist some bug(s) when using buttons on spreadsheet. Recently it was found out that they are thin lines when hiding/unhiding the underlying rows. Let's see if we can reproduce it or not.


    Kind regards, Dennis (http://xldennis.wordpress.com/)
    Tuesday, October 12, 2010 11:01 AM
  • Hi Dennis,

    Thanks for the answer.

    Version of Excel 2010: professional version 14.0.4760.1000 (32 bit)

    Command buttons in use: as far as I can tell the user has experienced this so far with radio buttons and dropdown.

    The files are saved in Excel 97-2003 workbook format

    I have found on other blogs the exact description of this problem (in 2006), but unfortunately no solution was given.

    Thanks again

    Caroline

    Tuesday, October 12, 2010 11:50 AM
  • Caroline,

    Thanks for the information.

    No, I cannot reproduce it at all.

    I don't have any immediately fix to the problem but what You can do is to make sure that each control has the setting "Don't move and size with cells" selected and that the option "Print Object" is checked. To do so You right click on each control and from the pop up menu select "Format control..." and then select the tab "Properties".

    A workaround is to add a VBA procedure that resize all the controls to the wanted sizes and can be executed when this happens.



    Kind regards, Dennis (http://xldennis.wordpress.com/)
    • Marked as answer by Bessie Zhao Tuesday, October 19, 2010 9:27 AM
    Tuesday, October 12, 2010 4:25 PM
  • Thanks Dennis for looking into that. i really appreciate.

    I have to set the controls "move with cells" as my program deletes rows according to choices in a menu.

    Do you have a procedure already written that resize controls. if not do not worry I will write one.

    Thanks anyway

    Tuesday, October 12, 2010 4:34 PM
  • You're welcome but, sorry, I have no procedure for it in my toolbox.
    Kind regards, Dennis (http://xldennis.wordpress.com/)
    Tuesday, October 12, 2010 5:18 PM
  • Hi,

    Did you find a solution for the above issue? I am facing the same problem. Some of Active X control and Form control buttons shrink down and disappear. (I can manually resize them again). Most of the people in my group are using excel 2007. One of my colleagues recently shifts to Excel 2010. The Excel document which showed that behavior finally updated by her. But it not always does that. It's hard to replicate the problem. But we can see it very often.

     

    Any idea?

     

    Thanks

    Monday, December 20, 2010 10:55 PM
  • I also had the same problem in one of my user's machine. The controls (option btns, check boxes) on the worksheet either shrink or expands by clicking each time on the control.

    The way I fixed it with following methods:

    1. Make sure no other workbook is open.

    2. Worksheet zoom to set it around 80%.

    3. Restart the computer.

    Sometimes it happened while the laptop was connected to a projector. 

    Now its been few months and I never heard about that issue from the user.

    Hope this will help you.

    Tuesday, December 21, 2010 1:54 AM
  • Caroline, I think you are using a FormControl, rather than an ActiveXControl.  Try this:  right-click the control, click Format Control, click Properties, click Don't Move or Size with Cells.

    Regards,

    Ryan---

    Tuesday, December 21, 2010 5:27 PM
  • Ryan,

     

    Issue is with both Active X and form controls. They automatically shrink and move to the left of the excel sheet. (Excel 2010)

    Tuesday, December 21, 2010 9:56 PM
  • Ah!  In Design Mode...Right-Click > Properties > Placement = 3

     

    Wednesday, December 22, 2010 10:11 PM
  • I tried the "Placement = 3" fix and "Don't Move or Size with Cells" property as suggested above. No success. The fonts on my buttons in Excel 2010 Pro shrink every time I press them. This is true most of the time - I had instances when both the font and button size increased everytime I clicked them - I am not aware of anything being different each time I try this. 
    Thanks,
    Heiko

    Monday, February 14, 2011 9:47 PM
  • Hi 

    Has anyone found a solution to the resizing of Form Controls in Excel 2010?

    If you have a macro to resize the controls could you share the code?

    Thanks

    Carl

     

    Tuesday, April 19, 2011 10:48 PM
  • Hi,

    I had the same issue. Both activex and form controls automatically shrink and moves to the top left of the sheet in excel 2010 workbook.

    Below are the steps i ve followed to fix this issue:

    i) In design view, select all the controls in the sheet.

    ii) Right click and select Group->Group to group the controls, then save the workbook.

    Hope this helps!

     


    SeLvA
    • Proposed as answer by MCXTRA Wednesday, April 4, 2012 3:45 PM
    Thursday, September 22, 2011 2:43 PM
  • Hi

    I ended up logging a ticket with MS support.  They have released a private hotfix which fixed the problem. I am told by MS support a public hotfix is due out at end of Oct 2011.

    Fingers crossed this release date will happen as its been delayed once already.

    Regards

    Carl

     

    Thursday, September 22, 2011 3:00 PM
  • Hi Carl,

    Will MS give out the private hotfix if I log a ticket with them?

    Thanks.

    Lisa

    • Proposed as answer by viksbh Friday, October 14, 2011 1:27 PM
    • Unproposed as answer by viksbh Friday, October 14, 2011 1:27 PM
    Monday, October 3, 2011 9:32 PM
  • Hi Carl,

    Can you please let me know how can i log the ticket with microsoft? I am facing the same problem since long and tried every work around.

    Please let me know how can i get that hotfix?

    Thanks very much in advance.

    Best Regards,

    Vikas

    Friday, October 14, 2011 1:26 PM
  • Microsoft might give out a private build if you call them (http://support.microsoft.com/contactus/?ws=support).  

    I am due an update from Microsoft soon as to delivery date of the public hotfix. 

    Regards

    Carl

     

    Friday, October 14, 2011 4:28 PM
  • I have seen this issue and noticed the following conditions for that to happen:

    1) You are using a font that is not included in the "target" computer, and when the button is drawn on the screen it adapts and grows. Unfortunatelly, the font gets bigger also with the new appeareance but the information about it does not, so when you open it again it only gets bigger and bigger.

    2) Your desktop is configured for a screeen font size different than standard (windows display configuration)

    Both issues are bugs but you can work with that until a fix is provided.

    Friday, October 14, 2011 8:31 PM
  • I am having similar issues. Note that the thread seems to reference only buttons with text, but the buttons I am using all have images assigned rather than text.

    I am using Office 2007 Excel. I have a worksheet with several Active X buttons with VBA code assigned to each.

    Clicking any of the buttons causes the associated image to shrink; although the buttons usually keep the original button size, they do sometimes resize as well, reducing size, not increasing.

    I have tried all the workarounds listed in this thread with no success - Anyone have any other suggestions? My machine is on a corp network, and getting patches and so on is problematic.

    Wednesday, January 4, 2012 9:02 PM
  • I am having similar issues,

    I have tried a few things, but the main way to reproduce the error is if possible to try it on a Remote Desktop, and first try it in a forced 4:3 screen resolution (1280x1024 tried), and then in 16:9 screen resolution (1920x1080 tried), error is in 16:9 but not in 4:3 screen resolutions.

    Same issue on both Office 2007 and 2010.

    Regards
    Frank Sonne
    f/Kim Knudsen

    Wednesday, January 25, 2012 2:06 PM
  • Same problem as described above.  Never happens when using built-in display on laptop; happens frequently when docked and using the nice large monitor.  Happens with command button, combo box, and even label!  Any ActiveX control.  No problem with older, Forms controls.

    Found a workaround in another user group.  Have tested the workaround with several controls.  I can turn off and on the problem by adding or deleting the following code (on the code window of the worksheet containing the control)

    Private Sub ComboBox1_click()
        ActiveCell.Activate
        With Me.ComboBox1
            .Height = .Height + 1
            .Height = .Height - 1
        End With
    End Sub

    Hope it helps

     - Pete

     


    • Edited by Pete770 Monday, January 30, 2012 6:31 PM
    • Proposed as answer by GermanchoCoder Friday, May 17, 2013 5:27 PM
    Monday, January 30, 2012 6:31 PM
  •  I just noticed that my initial question triggered some interest and that a lot of people have experienced the same problem. I have found since the source of the problem, but no real solution. Nevertheless I thought I might share this with you.

    This is a Microsoft problem.
    When you change the display resolution while Microsoft Office Excel is running, controls change size when you click them. The controls become larger or smaller.

    It happens when changing the screen resolution while your workbook is opened. For instance if the workbook is opened and you link your computer to a projector.
    To avoid this problem, close the workbook before you change your screen resolution (when you link it to a projector or a docking station).
    If this happens, close the workbook without saving, close Excel and re-open it.

    Thursday, February 9, 2012 4:26 PM
  •  I just noticed that my initial question triggered some interest and that a lot of people have experienced the same problem. I have found since the source of the problem, but no real solution. Nevertheless I thought I might share this with you.

    This is a Microsoft problem.
    When you change the display resolution while Microsoft Office Excel is running, controls change size when you click them. The controls become larger or smaller.

    It happens when changing the screen resolution while your workbook is opened. For instance if the workbook is opened and you link your computer to a projector.
    To avoid this problem, close the workbook before you change your screen resolution (when you link it to a projector or a docking station).
    If this happens, close the workbook without saving, close Excel and re-open it.

    I struggled with this issue also.

    This worked a treat. Thanks for the help!

    Tuesday, February 21, 2012 12:17 PM
  • Here is a nice solution made by my colleague Levon:

    Sub CommandButton1_Click()
    	CommandButton1.Visible = False
    	CommandButton1.Visible = True
    ...
    End Sub



    • Edited by Geaden Thursday, March 22, 2012 6:53 AM
    Thursday, March 22, 2012 6:41 AM
  • Problem Fixed! Best answer of all!  Thanks SeLvA01!
    Wednesday, April 4, 2012 3:46 PM
  • I am having the same problem frequently: when logging in remotely (Citrix) to a clients network and running Excel (2003) with commandbuttons and spinbutons growing in size. Other users, working via Remote Desktop, report the same problem. Will try the suggestion posted by Geaden below.

    Wednesday, April 4, 2012 4:03 PM
  • thought I'd beaten this some time ago, only to have it come back with a vengance now.

    Seems to be tied to monitor /change of resolution. But its been going on 4 years now, you'd think microsoft would release a fix already ! - I mean a public one, not just a private one.

    • Proposed as answer by Caribfb Wednesday, April 25, 2012 5:23 PM
    • Unproposed as answer by Caribfb Wednesday, April 25, 2012 5:23 PM
    Sunday, April 22, 2012 4:06 PM
  • Grouping of the controls seems to have solved the problem for me. I am running Windows 7 Professional and excel 2010.

    Of course you should have more than one control to group otherwise create a dummy control and group with that.

    No shrinkage or movements of the controls observed !


    • Edited by Caribfb Wednesday, April 25, 2012 5:27 PM
    Wednesday, April 25, 2012 5:26 PM
  • Grouping *may* be a workaround for the button resizing, but I'm yet to find a solution to the shrinking fonts, as described by Hlanger above.

    Now that they've shrunk on several buttons, there seems to be no way to grow them back to size, apart from setting them to an absurdly large font.

    I guess I could re-create my buttons AGAIN....form buttons aren't an option since I rely on button colours as part to the interface.

    I'm running win 7 pro 64 bit, Office pro 2010.

    Thursday, April 26, 2012 7:57 AM
  • Have you tried this one for 2010?

    http://support.microsoft.com/kb/2596494

    Thursday, June 7, 2012 10:27 AM
  • this should work

    Group your command buttons

    then add code below to each buttons code

        '***************** Fix Text Shrinking **************
        ActiveSheet.Shapes.Range(Array("Group 3")).Select
        Selection.ShapeRange.Height = 30
        Selection.ShapeRange.Height = 21.6

    Monday, July 2, 2012 9:21 PM
  • As an extension of Kmann's answer, this should work for any button to fix the text, but it's a bit better because you don't have to know the name of the group and you don't need to change it per button depending on the button height (you may still need to set the button height first if that is changing for you, if either the grouping method does not work for you or if you don't want to group and select the buttons for whatever reason):

    ActiveSheet.Shapes("CommandButton1").ScaleHeight 1.25, msoFalse, msoScaleFromTopLeft
    ActiveSheet.Shapes("CommandButton1").ScaleHeight 0.8, msoFalse, msoScaleFromTopLeft

    Monday, August 13, 2012 6:52 PM
  • That article has nothing to do with this issue.

    Doug Pruiett Good News Jail & Prison Ministry Richmond, Virginia www.goodnewsjail.org

    Tuesday, October 2, 2012 6:43 PM
  • I have tried eveything posted here and nothing worked.  My best workaround was to replace all my command buttons by rectangular shapes and affect them macros.

    I did not have any oher control than the command button.  If I had had, this would have been another issue.

    Thursday, November 1, 2012 6:56 PM
  • We had this issue in house and a lot of our customers using Excel 2010 reported the same issue.
    Opened a ticket with Microsoft and worked with them for 4-5 months and below is the solution they gave us.  It has resolved the issue for 3 out of 3 of our customers; so I think this should work for everyone.

    Remember to make the registry changes after the hotfix patch is applied!

    Step 1: Make sure you have the latest Excel 2010 Service Pack and all subsequent updates installed.

    Step 2: Download, extract and install the correct (32-bit or 64-bit) Hotfix package. You can download the HotFix from KB 2598144: http://support.microsoft.com/kb/2598144

    Step 3: Make registry changes as described below:

    Quit Excel.

    Click Start, click Run or use the start search box, type regedit , and then click OK.

    Locate and then click to select the following registry key:

    HKEY_CURRENT_USER\Software\Microsoft\Office\14.0\Excel\Options

     

    After you select the key that is specified in step 3, point to New on the Edit menu, and then click DWORD.

    Type LegacyAnchorResize , and then press ENTER.

    Right-click LegacyAnchorResize, and then click Modify.

    In the Value data box, type 1 , and then click OK.

     

    Select the Options key that is specified in step 3 again, point to New on the Edit menu, and then click DWORD.

    Type MultiSheetLegacyPrint , and then press ENTER.

    Right-click MultiSheetLegacyPrint, and then click Modify.

    In the Value data box, type 1 , and then click OK.

     

    On the File menu, click Exit to quit Registry Editor.


    • Proposed as answer by RBAFF79 Tuesday, November 13, 2012 8:36 PM
    • Edited by RBAFF79 Tuesday, November 13, 2012 8:40 PM service pack number could change
    Tuesday, November 13, 2012 8:35 PM
  • I got this from another forum, it's simple and it works:

    This type of Controls re-sizing has been a problem for some time. From some of my research it appears that it might be due to the zoom setting of the worksheet not at 100% but not sure of this.

     

    However, the workaround is to have a Workbook Open event that resizes each time the workbook is opened. The following code contains code for ActiveX and Forms controls. Only use the required part of the code. Open the VBA editor and double click ThisWorkbook in the project explorer and copy the code into the editor. Edit the worksheet name and control dimensions and position to suit.  (Do not change the sub name or it will not work.)

     

    Private Sub Workbook_Open()
        'Following code for ActiveX control
        With Sheets("Sheet1").OLEObjects("ListBox1")
            .Left = 10
            .Top = 10
            .Width = 50
            .Height = 100
        End With
           
        'Following code for Forms Control
        With Sheets("Sheet1").Shapes("List Box 2")
            .Left = 100
            .Top = 10
            .Width = 50
            .Height = 100
        End With
    End Sub

    Friday, March 15, 2013 7:04 PM
  • Hello:

    Regarding XL-Dennis's solution, I'm glad I stumbled on this.  I'm not sure why Excel (in my case) shrinks the button to almost non-visible, but as recommended, before the exit the Sub that creates the issue, I resize the button to the proper width, height, top and left.

    At least there is a workaround!!

    Regards,


    Rich Locus, Logicwurks, LLC

    http://www.logicwurks.com

    Saturday, March 16, 2013 4:26 AM
  • This occurs when the user has a docking station.
    Friday, May 17, 2013 5:25 PM
  • ' In a module put this:

    Sub RecoverSizes()
    Dim sh As Shape

    For Each sh In ActiveSheet.Shapes
        With sh
            .Height = .Height + 1
            .Height = .Height - 1
        End With
    Next

    End Sub

    'In every worksheet code include this:

    Private Sub Worksheet_Activate()
    RecoverSizes
    End Sub

    Friday, May 17, 2013 5:29 PM
  • my problem is the commandbutton enlarging when click, so

    i used

    Private Sub CommandButton1_Click()
    forecast

    commandButton1.height=200 'commandButton1's orignial size

    commandbutton1.width=600

    End Sub

    Monday, June 17, 2013 1:42 PM
  • I can answer this question easely, i scrolled Down to see if any other People has written the sollution, but i failed to find.

    Anyway, the problem is wierd at best and is easely fixed... The cause of the problem is simple, its basicly based on what resolution Your running Your desktop on.. ie if 1920x1080 is Your native, but you use a lower resolution like 1024x768 or any resolution but 1920x1080, then ActiveX Objects gets messed about.

    Simply set Your res to native (1920x1080) if thats Your max res.

    Wednesday, November 27, 2013 2:50 PM
  • Hello,

    I had this problem, so I did what it is proposed and works perfectly:

    Private Sub CommandButton1_Click()

        ' Avoid problems with screen resolution changes...
        CommandButton1.visible = False
        CommandButton1.visible = True
        ActiveSheet.Shapes("CommandButton1").ScaleHeight 1.25, msoFalse, msoScaleFromTopLeft
        ActiveSheet.Shapes("CommandButton1").ScaleHeight 0.8, msoFalse, msoScaleFromTopLeft

        ' ...

    End Sub

    Thanks a lot!

    Monday, March 25, 2019 10:45 AM