Buttons Don't Stay Put

by Allen Wyatt
(last updated August 17, 2013)

22

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

Changing between English Variants

What is the easiest way to switch between English spelling variants in a document? This tip examines a couple of ways you can ...

Discover More

Saving a Document in a Macro

If you develop a macro to process your document, you may want the macro to save the document to disk. This is easily done ...

Discover More

Calculating a Date Five Days before the First Business Day

Excel allows you to perform all sorts of calculations using dates. A good example of this is using a formula to figure out a ...

Discover More

Program Successfully in Excel! John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!

More ExcelTips (ribbon)

Out of Memory Errors when Accessing the VBA Editor

It can be frustrating when you get error messages doing something that you previously did with no errors. If you get an out ...

Discover More

Selecting the First Cell In a Row

When creating macros, you'll often have a need to select different cells in the worksheet. Here's how to select the first ...

Discover More

Determining if Calculation is Necessary

When processing a worksheet with a macro, it may be helpful to periodically recalculate the worksheet. Wouldn't it be nice if ...

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. 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 9 + 4?

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.