Buttons Don't Stay Put

by Allen Wyatt
(last updated November 20, 2017)

26

Gary uses a lot of buttons to trigger macros in his workbooks, and for some reason they move all over the place and resize themselves. He wonders why this happens and how he can stop it. He wants the buttons to stay the size he chooses, in the place he specifies.

Believe it or not, Excel allows those buttons to move and resize by default. If you don't want them to behave in that way, then you need to take steps to nail them down. If your buttons are actually form control buttons or ActiveX command buttons, then you simply need to right-click on the button and choose Format Control from the resulting Context menu. Excel displays the Format Control dialog box, on which you should select the Properties tab. (See Figure 1.)

Figure 1. The Properties tab of the Format Control dialog box.

Just click the radio button that reflects the behavior you want for the control—in most cases you'll click Don't More or Size with Cells.

If the buttons you are using are actually shapes, clip art, or a text box that you've assigned macros to, you can follow the same general steps. Right-click the object and choose Format Shape (or, if it is available, Size and Properties) from the Context menu. Excel 2007 and Excel 2010 display the Format Shape dialog box, while Excel 2013 displays the Format Shape task pane.

  • If you see the Format Shape dialog box, make sure the Properties tab is displayed.
  • If you see the Format Shape task pane, click on Shape Options | Size & Properties and expand the Properties section.

Set the movement and resizing radio buttons to, again, reflect the behavior you want for your object.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (7009) applies to Microsoft Excel 2007, 2010, and 2013.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He  is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Checking for Valid Hyperlinks

If you have a document containing a lot of hyperlinks, it would be cool if there was a way to check all those hyperlinks and ...

Discover More

Centering a Table

Left-justified tables are great for many document designs, but you may want instead to center a table between the margins of ...

Discover More

Unwanted Hyperlinks

Tired of having Excel convert what you type into active hyperlinks? Here are things you can do to undo Excel's conversions or ...

Discover More

Comprehensive VBA Guide Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out Mastering VBA for Office 2010 today!

More ExcelTips (ribbon)

Running a Procedure when a Workbook is Opened

Ever want to have Excel run a procedure whenever you open a workbook? It's not as difficult as you might think. Here's how.

Discover More

Item Not Available in Library

When sharing workbooks with others, you may find that the macros in those workbooks may not work as you expect. This tip ...

Discover More

Finding Positions of Formatted Characters in a Cell

With a little bit of work, Excel allows you to format individual characters of the text you place in a cell. If you want to ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is 2 + 1?

2017-11-19 16:33:41

Mandora

Sample Options For Anchoring Excel Command Buttons And Other Controls

1. The following sample code anchors a command button to a designated cell defined by a range name. Using a range name for cell allows location to change if, for example, rows are added or deleted.
Sub Account_100_U_()
Dim rng As Range
Set rng = Sheet3.Range("ACCT_100_U") 'Cell range name
With Sheet3.OLEObjects("btn100_U_DS")
.Top = rng.Top
.Left = rng.Left
.Width = 84#
.Height = 25#
End With
End Sub

A variation when cell location is fixed.
With ActiveSheet
Set btn = .OLEObjects.Add(ClassType:="Forms.CommandButton.1", _
Left:=.Range("A87").Left, Top:=.Range("A87").Top, Width:=340, Height:=30) 'Set button to top-left of specified cell
End With
_____________________________________________________________________
2. Header row with grouped buttons, freeze pane on. Grouping buttons prevents buttons moving or collapsing to zero height during filtering, Show/Hide Ribbon, etc.

_____________________________________________________________________
3. Header row with a userform “DataSheet Controls” with various command buttons. Prevents command buttons from moving and/or resizing. If Ribbon is to be toggled Show/Hide, the following code maintains DataSheet Controls in the upper-left of header row

Sub Ribbon_Show()
'Displays Ribbon and toggles Label on btnToggleRibbon.
Call Workbook_Deactivate 'Show Ribbon
Application.ScreenUpdating = False
frmControls.btn_ToggleRibbonOnOff.Value = True
frmControls.btn_ToggleRibbonOnOff.Caption = "HIDE RIBBON"
With frmControls
.Top = Application.Top + 153 '< change as necessary
.Left = Application.Left + 20 '< change as necessary
End With
Application.ScreenUpdating = True
End Sub
Sub Ribbon_Hide()
'Hides Ribbon and toggles Label on btnToggleRibbon.
Call Workbook_Activate 'Hide Ribbon
Application.ScreenUpdating = False
frmControls.btn_ToggleRibbonOnOff.Value = False
frmControls.btn_ToggleRibbonOnOff.Caption = "SHOW RIBBON"
With frmControls
.Top = Application.Top + 50 '< change as necessary
.Left = Application.Left + 20 '< change as necessary
End With
Application.ScreenUpdating = True
End Sub
_____________________________________________________________________
4. Set Position/Size of Grouped Header Row Buttons (Not anchored to cells)

'Sheet Protect/Unprotect
Sheets("JAN").Shapes.Range(Array("ToggleProtection_JAN")).Visible = True
Sheets("JAN").Shapes("ToggleProtection_JAN").Height = 40#
Sheets("JAN").Shapes("ToggleProtection_JAN").Left = 104
Sheets("JAN").Shapes("ToggleProtection_JAN").Top = 6
Sheets("JAN").Shapes("ToggleProtection_JAN").Width = 109.2
'Clear Sheet Data
Sheets("JAN").Shapes.Range(Array("btnClearData_JAN")).Visible = True
Sheets("JAN").Shapes("btnClearData_JAN").Height = 40#
Sheets("JAN").Shapes("btnClearData_JAN").Left = 228
Sheets("JAN").Shapes("btnClearData_JAN").Top = 6
Sheets("JAN").Shapes("btnClearData_JAN").Width = 116
'Save-As Dialog
Sheets("JAN").Shapes.Range(Array("btnSaveAsDialog_JAN")).Visible = True
Sheets("JAN").Shapes("btnSaveAsDialog_JAN").Height = 40#
Sheets("JAN").Shapes("btnSaveAsDialog_JAN").Left = 359
Sheets("JAN").Shapes("btnSaveAsDialog_JAN").Top = 6
Sheets("JAN").Shapes("btnSaveAsDialog_JAN").Width = 109.2

_____________________________________________________________________
5. If buttons' height become resized to zero, they can be resized using the following code run from the sheet module. Sizing can be fixed using a method above.

Sub ShowEachShape3()
Dim sObject As Shape
For Each sObject In ActiveSheet.Shapes
sObject.Visible = True
sObject.Height = 40#
Next
End Sub



2017-11-15 10:41:40

Dave Bonin

The screenshot is a little small. Maybe this will look better.

(see Figure 1 below)

Figure 1. Control Panel Screenshot


2017-11-15 10:38:21

Dave Bonin

I took an entirely different approach to buttons... I use shapes and then assign macros to them to do my bidding.

Here's a screen shot of my control panel for a large workbook I update every month.

(see Figure 1 below)

The "buttons" all stay put.

Being a little on the cautious side (and somewhat fussy), I wrote a VBA procedure to re-size, re-name and re-position each shape based off the cell it's anchored to. It's attached to the pink "Rebuild Buttons on this Sheet" shape in the lower right corner. This procedure crawls through all shapes on the sheet and tweaks each. This is especially helpful when I add or move buttons. Press and Presto! All buttons are re-aligned automagically.

Figure 1. Control Panel Screenshot


2017-11-15 06:19:59

Jeffrey Pfouts

We are using check boxes in one of our forms. Every time I do a save as the check boxes disappear. I have never had this happen in any forms that I have authored but, this form is from someone else and I cannot figure out for the life of me what was done differently.

Anyone have any thoughts?

Thanks!!


2017-03-15 13:27:59

Dennis Jackson

Hi:

This tip used to work, but only with Form buttons. Activex command buttons resize and move after a print no matter what you do. I've also noticed recently that this feature has extended to Form buttons, as well. No matter what options you use Form command buttons will resize and move after a print. Oddly enough, this doesn't show up until you save your worksheet and reopen it.

Could be this is just a feature of Excel 2010.

DJ


2017-01-23 14:08:50

Brad

I have command buttons (form controls) which are already set to not move or resize with cells. However, they are effected by fltering data. ie, if i filter data and the result si too few rows to fit the buttons, excel hides or resized the buttons to fit the resulting few rows of data, and it does not restore the buttons when I unfilter.


2016-12-30 13:17:13

Colin

Hi, this tip did help thank you.

Richard Zawadzki, your explanation makes the most sense. Thank you.

However, when my excel sheet is opened on a Mac the check boxes still move slightly out of place. Is this due to the same affect as Richard explained?

Is there a means of overcoming this when opened on a Mac?

Thanks for you help!


2016-11-29 13:26:47

Harry

The appropriate VBA property to keep it in place and prevent it from re-sizing is (at least in Office 2010)

ObjectName.Placement = 3

For those who might be interested, setting that value to '1' is the default move and resize the button, and the value '2' is move but don't resize.


2016-01-21 20:00:20

Shirley

What about when the button completely disappears? I had print buttons for notification letters. Work with it today. Saved and closed the workbook. Opened later and not one button shows up.

How would you get that back?


2016-01-04 18:29:33

Tim

Reported this to Microsoft several times (have seen it over the years, in different versions). Their initial suggestions to service pack and upgrade just don't work. Eventually their product team acknowledged the bug (early 2015), but was told they have not prioritised to fix at this stage.


2015-12-15 03:26:44

Ron

I run into the same problem.

Grouping of the buttons solved part of the problem for me, only one of three buttons was now fixed, the other two still changed in shape.

I found a solution (too stupid too be true, but it worked...)

after grouping the buttons, I added the following code:

ActiveSheet.Shapes.Range(Array("Group 1")).Select
Selection.ShapeRange.IncrementLeft 2.25
Selection.ShapeRange.IncrementLeft -2.25

So just move the group and move it back....

It worked for me! Finally.


2015-12-04 05:17:55

Simon

Thank You


2015-10-21 14:45:59

Adam

I don't remember when or where I came across this solution, but here you go:

First, make sure your object is marked Move but don't resize with cells.

Then put this in your VBA code:

Set MyRange = ActiveSheet.Range("A2")

'change A2 to the cell where the the button/box should always be located

Then you can use the following in your macros, just change ObjectName to the name of your object:

ObjectName.Top = MyRange.Top

This will automatically move the top of your object in line with the top of cell A2. You can set this code up to run anytime a cell is changed, so it constantly keeps the object there.

You can also change .Top to .Left if you have issues with your object moving left and right. Also, if your "A2" is dynamic, you can run add some sort of formula to determine what that range should be.


2015-09-28 09:36:26

Morgan Ball

The solution put forward by F works. Grouping you form controls and buttons together or to other shapes in the workbook stops them being relocated :).


2015-09-02 03:34:01

Tham

I used the button to refresh one pivot table. But every after I click the button, the working sheet move to the pivot table sheet. Can I click the button (refresh the pivot table) and stay at the current sheet?


2015-07-21 00:19:52

Bob Jones

I lock the movement on my buttons but when I apply the 'Data Filter' all of my Active X Controls move to the left hand side of the page on top of each other! WTF!


2015-05-28 13:50:25

nobody

"Don't move or size with cells" still does not work properly with office 2013 and latest updates. The only options are under "Format Shape" which have fewer controls available than in older versions of excel. There should be a Position set of controls where the user can set a fixed position in pixels or inches from the upper left or right, lower left or right, or center of the visible sheet, with the control point of the object being selectable with the same parameters. When a fixed position is set, no activity whatsoever should affect that position. If I choose to make the top row blank at a fixed height, and choose to top-center a button on the visible worksheet, no resizing of the window, or scrolling should affect that it is centered and at the top of that worksheet. Microsoft continues to remove functionality and flexibility, while adding a lot of useless wizards and widgets to impress idiots.


2015-04-20 13:06:45

Charles

There are a lot of people commenting answers that do not seem to understand the problem.

Here it is:

1. You install a button on a Sheet.
2. You format it so that it does not change size or move with Cells.
3. You perform some operation with the workbook (maybe printing, maybe preview, maybe save). The next time you look at the sheet or open the workbook the BUTTON HAS MOVED AND RESIZED.

This is not a screen tearing type issue that Jay raised.

This is not a matter of buttons not being observed or a disappearing problem like Aldo has solved.

It is not a matter of setting the properites as Chuck as said -- after all, we DID that!

And it is not related to hiding or unhiding rows as Kathleen as asked about.

It is a BUG in Excel that has been there for years and they never fix it.

I think the person named "F" May have a solution. You can also use a VBA code on each sheet to "fix" the cells each time the page is opened.



2014-12-01 02:31:12

Jay

I used a combobox and on expanding the combo box it shows all the listed items.
But in case I do not select any for the item from the list and scroll excel up or down, everything scrolls alongwith but not the expanded list of combo box.
It seems to be stuck at its position.
Please advise.
Thanks in Advnce!


2014-06-24 09:18:00

F

I've had the problem when I go to Print Preview (Ctrl + P), save and close the document. When I reopen my excel file, all ComboBoxes are moved. I found a different solution that worked (for me): select all ComboBoxes, either by pressing ctrl and click all the boxes or by using the 'select objects pointer'. Group the ComboBoxes. Now they stay put when I go to the Print Preview.


2013-08-21 11:34:21

Richard Zawadzki

Many persons have reported issues with Excel buttons resizing and fonts shrinking. This has nothing to do with the "Don't Move or Size" option.
The problem seems to relate to the way Windows handles non-native resolutions on monitors and/or external projectors.
The cause seems to relate to any setup where screens are used in something other than their native resolution. This can happen easily if a user plugs an external monitor into a laptop, or a laptop into a docking station, and doesn't choose the resulting screen configuration carefully. Incorrect settings almost always cause serious problems with Excel buttons, especially ActiveX controls. Sometimes, on repeated clicks, the font can shrink to unreadable size; other times they expand to cover the whole screen.
ActiveX controls can be changed after you print preview, print, or save a workbook. They can also 'blink' annoyingly.
Microsoft has issued a hotfix, but it is seriously tedious to implement.
There are some solutions, which work for some, e.g. just grouping all controls, or using vba to adjust all activesheet shapes e.g. by using.. shape.Height = shape.Height + 1
shape.Height = shape.Height - 1

I believe the issue affects those that have recently upgraded from Excel2003 to later versions.

Check this link:
http://blogs.technet.com/b/the_microsoft_excel_support_team_blog/archive/2012/11/27/activex-and-form-controls-resize-themselves-when-clicked-doing-a-print-preview-or-resolution-changed.aspx

RZ


2013-08-19 13:11:06

Walter

I use check boxes throughout a workbook that has about 40 pages/tabs. Mostly, yes/no questions throughout a worksheet so I can't "Freeze Panes" rows, columns or otherwise in a specific area as suggested by Aldo. I have had this problem for years, doesn't matter which version of Excel or Windows I have used. Currently using Windows 7 and Office 2010. Have tried the "Don't Move or Size" and this doesn't work, these Properties options are useless. Chuck mentions changing VBA code, don't know how to do that. I've posted this question a few times the last couple of yrs, if someone has an answer I would be forever in your debt!


2013-08-19 10:11:37

Gary Lundblad

I checked my buttons, which are "Form Control Buttons," and they are set to "Don't move or size with cells." They are also in a part of each worksheet at the top, above the "Freeze Panes" line, so I am not sure why they would move and resize to begin with. I do not hide any rows or columns in the area where the buttons are, and I don't resize those areas either. It is a total mystery.


2013-08-17 13:32:19

Aldo

I lock a set of rows at the top of the sheet with Freeze Sheet Panes and put the buttons and switches in this area so they are always visible and usable while scrolling up and down the sheet.


2013-08-17 11:39:26

chuck

Make the properties of the buttons so they do not move or size with the cells and then in VBA code set visible to "true" when you want them down and "false when you don't.


2013-08-17 10:35:57

Kathleen Lowell

I have had some trouble with buttons resizing when on rows or columns when hidden, then not resizing when unhidden. Is there a way to have buttons for rows or sections that are hidden?


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.