Excel Prevent Buttons, Shapes and Objects from Resizing

Are you fond of using Dashboards in Excel? I know you would say “Yes” and why not everyone likes it. It is one of the beautiful things that can be done using Excel. So, we would have many objects to make Dashboard looks pretty. In that, we would want to insert a header, new rows or change the size of the row. Believe me most of us have faced many problems while doing one of the tasks I have mentioned.

Problem: Buttons, Shapes and Objects Resizes

We would see that objects on the Dashboard would get resized by doing one of the tasks mentioned above. Objects of the Dashboard get disturbed when we do not set size and properties of them. If you are one among many who is thinking for a solution to overcome such problem, then this article is for your guys. Yes, Rick Grantham has come up with the best ever Excel tip to prevent buttons, shapes and objects from resizing. Here we go!

1 – Add Shapes to Dashboard

1

In order to know the Excel tip to prevent buttons, shapes and objects from resizing, first let us insert a shape on the Dashboard.

On the Toolbar, click “Insert” and from “Shapes” select one, say “Rounded Rectangle”. Just click and drag in Excel to place the selected shape as shown and give it a name.

2 – Set Size and Properties

Now, as our aim is to help you in preventing buttons, shapes and objects from resizing we need to set size and properties of the shape which we have inserted. This is the important step in this and very few know about this.2

But, Excel TV reveals it for you folks! Let us do that. Right click on the inserted shape and click “Size and Properties”.

3 – Object Positioning

Now, you could see “Format Shape” dialog box has been popped up. On the left side, click “Properties” and select the radio button or option “Don’t move or size with cells” and click “Close”. There are other two options available which can be selected based on your requirement. This is the magic and also the solution to your problem.3

4 – Try and Test

Now, let us test it. Just try to insert new rows or change the size of rows, you could see that inserted buttons, shapes, objects in your Dashboard were never resized. Awesome! Is this not you are looking at? Hope you are happy that you have got solution now. Go and attempt it!

Let us thank Rick Grantham for sharing this amazing Excel tip with us. If you have anything to share, please do let us know through comments.

Sridhar Belide

Tags


You may also like

April 9, 2011

How to Create a Rollover Effect in Excel: Execute a Macro When Your Mouse is over a Cell

October 26, 2014

Monte Carlo Simulation Formula in Excel – Tutorial and Download

September 17, 2012

The Excel Rollover Mini FAQ
  • No, it doesn’t always work. Really annoying bug. I usually have to go back and reset the many buttons/shapes several times before they seem to stay put. Another BS bug that should have been corrected.

  • It seems to work for me – at least much better than before.

    What did it for me so far is:

    a) setting the object to only move, but not size
    b) locking the ratio (in the size tab)
    c) setting the individual properties of the buttons (which were affected in my case) via the properties dialog such that the placement property is set to 3 (xlPlacement =3 –> xlFreeFloating, Object is free floating –> https://docs.microsoft.com/en-us/office/vba/api/excel.xlplacement)

  • {"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

    Free 24 Excel Tips Guide

    Discover the secrets to Excel efficiency with our free guide: '24 Proven Microsoft Excel Tips to Help You Save Time'

    A humble gift from us to you, to make your Excel journey smoother and more productive. Grab your copy now and start mastering Excel like never before!

    >