Can you have the height and width of a rectangle in Excel change in response to formula cells in the worksheet? Episode 1899 shows a Worksheet_Calculate event handler that does the trick.
Transcript of the video:
The MrExcel podcast is sponsored by Easy-XL.
Learn Excel from MrExcel podcast, episode 1899.
Resize Rectangle Based on Formula Cell Results.
Hey, welcome back to the MrExcel netcast.
Today's question sent in by YouTube.
I've created a shapes rectangle in Excel and can edit the size, you know, height and width, but I would like this to automatically resize, based on these formula results.
Is that possible?
All right, yes, it's possible, but you have to use a VBA macro.
So in order to use VBA macro, couple of things.
Alt, T, M, S, make sure that you are not set to the highest security there, otherwise you can't use macros.
Also, if your file is stored as xlsx, which is very, very common, you have to do File, Save As and save as xlsm or xlsb.
Xlsx is not allowed to have macros, all right.
First thing we have to do, is figure out the name of this, looks like it's Rectangle 1, but you know what, I'm lazy, I'm going to record a macro, it's going to be called HowToResize, store it in this workbook.
And I'm just going to select the shape and I'm going to resize it.
Resize it and then stop recording, that way I change the both, the height and the width.
Alt+F8 to take a look at that code HowToResize, Edit.
Alright, so here's how they refer to it: ActiveSheet.Shapes.Range(Array(“Rectangle 1”)).Select.
And then, shoot, they got me, they actually use the ScaleHeight and ScaleWidth methods, not what I expected them to do, but at least I know now the name of how they refer to this.
ActiveSheet.Shapes.Range(Array(“Rectangle 1”)).Select., although we don't actually have to select it.
Now the trick is: as this worksheet recalculates, you want to resize the rectangle.
So the code can't live here on Module1 or Module2.
Instead the code has to live on the worksheet.
So in the Project Explorer here I double click on Sheet 1, from the top left drop-down choose Worksheet and they automatically choose SelectionChange, but I want to open the top right drop-down and say no, I want this macro to run every time we Calculate, alright.
And I'll paste in some code here.
So this is from the recorded macro, although I took off the “.Select” and I added “With” before.
Anytime you have “With” and “End With”, all the other lines don't have to say “ActiveShee.Sshapes.Range”, you can just leave all that off and just put a dot.
So the dot says: hey, go back and append this property to the end of everything the “With”.
So I'm changing the Width, be my A2 value, changing the Height to be my A1 value, End Sub… Actually get rid of this now.
And that little recorded macro that I did, that was just a little tester, we can get rid of that.
If I want to, I can right click and remove Module2, so we just have that little bit of code.
And then to test it out, this is a =RANDBETWEEN variable up here, so every time that I enter something in a new cell, that will calculate and the shape will automatically update, alright.
Cool idea, simple a little bit of code to get it done.
Hey again, if you happen to be watching this on July 23rd or 24th, you have until I think 8 o'clock tonight on the 24th, 20% off for awesome, amazing Excel Dashboard Course, or until July 31st for 50% off.
All right, I want to thank you for stopping by, we’ll see you next time for another netcast – MrExcel.
Learn Excel from MrExcel podcast, episode 1899.
Resize Rectangle Based on Formula Cell Results.
Hey, welcome back to the MrExcel netcast.
Today's question sent in by YouTube.
I've created a shapes rectangle in Excel and can edit the size, you know, height and width, but I would like this to automatically resize, based on these formula results.
Is that possible?
All right, yes, it's possible, but you have to use a VBA macro.
So in order to use VBA macro, couple of things.
Alt, T, M, S, make sure that you are not set to the highest security there, otherwise you can't use macros.
Also, if your file is stored as xlsx, which is very, very common, you have to do File, Save As and save as xlsm or xlsb.
Xlsx is not allowed to have macros, all right.
First thing we have to do, is figure out the name of this, looks like it's Rectangle 1, but you know what, I'm lazy, I'm going to record a macro, it's going to be called HowToResize, store it in this workbook.
And I'm just going to select the shape and I'm going to resize it.
Resize it and then stop recording, that way I change the both, the height and the width.
Alt+F8 to take a look at that code HowToResize, Edit.
Alright, so here's how they refer to it: ActiveSheet.Shapes.Range(Array(“Rectangle 1”)).Select.
And then, shoot, they got me, they actually use the ScaleHeight and ScaleWidth methods, not what I expected them to do, but at least I know now the name of how they refer to this.
ActiveSheet.Shapes.Range(Array(“Rectangle 1”)).Select., although we don't actually have to select it.
Now the trick is: as this worksheet recalculates, you want to resize the rectangle.
So the code can't live here on Module1 or Module2.
Instead the code has to live on the worksheet.
So in the Project Explorer here I double click on Sheet 1, from the top left drop-down choose Worksheet and they automatically choose SelectionChange, but I want to open the top right drop-down and say no, I want this macro to run every time we Calculate, alright.
And I'll paste in some code here.
So this is from the recorded macro, although I took off the “.Select” and I added “With” before.
Anytime you have “With” and “End With”, all the other lines don't have to say “ActiveShee.Sshapes.Range”, you can just leave all that off and just put a dot.
So the dot says: hey, go back and append this property to the end of everything the “With”.
So I'm changing the Width, be my A2 value, changing the Height to be my A1 value, End Sub… Actually get rid of this now.
And that little recorded macro that I did, that was just a little tester, we can get rid of that.
If I want to, I can right click and remove Module2, so we just have that little bit of code.
And then to test it out, this is a =RANDBETWEEN variable up here, so every time that I enter something in a new cell, that will calculate and the shape will automatically update, alright.
Cool idea, simple a little bit of code to get it done.
Hey again, if you happen to be watching this on July 23rd or 24th, you have until I think 8 o'clock tonight on the 24th, 20% off for awesome, amazing Excel Dashboard Course, or until July 31st for 50% off.
All right, I want to thank you for stopping by, we’ll see you next time for another netcast – MrExcel.
