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
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.
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.
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.
- Edit Excel Formulas in WORD – Spreadsheet Tips and Tricks - February 17, 2022
- Using the Immediate Window in VBA - April 4, 2017
- Intersection Operator with Named Ranges – Excel Tips and Tricks - October 3, 2016
Doesn’t always work for some reason. Seems to be a bug in Excel.
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)
It is not working, it will still resize when you delete or edit the cells. 🙁