Automatically change shape size based on values in a cell

[jrv326s]

Hi everyone,

I've been wrestling with what is probably a simple problem. I have a map of the US on one worksheet with shapes (circles) over locations where our company has resources. I've put in a button on that sheet that toggles the shape on or off using the .visible = true or .visible = false properties. This button also has a caption that toggles as well. However, I also want to dictate the size of those shapes based on values entered in another worksheet in the same workbook. I've tried and just can't get it to work. Here's the kind of code I am using to toggle the visibility. I'd like to insert code into this event that will also adjust the circle size based on value in a cell. Thanks!

Private Sub CommandButton5_Click()
If CommandButton5.Caption = "Hide" Then
CommandButton5.Caption = "Show"
ActiveSheet.Shapes("Oval 1_LosAngeles").Visible = False
Else
If CommandButton5.Caption = "Show" Then
CommandButton5.Caption = "Hide"
ActiveSheet.Shapes("Oval 1_LosAngeles").Visible = True
End If
End If
End Sub

 

[pgc01]

Hi
Welcome to the board

For ex., using the value in A1, try:

Private Sub CommandButton5_Click()

If CommandButton5.Caption = "Hide" Then
    CommandButton5.Caption = "Show"
    ActiveSheet.Shapes("Oval 1_LosAngeles").Visible = False
ElseIf CommandButton5.Caption = "Show" Then
    CommandButton5.Caption = "Hide"
    With ActiveSheet.Shapes("Oval 1_LosAngeles")
        .Visible = True
        .Width = Range("A1").Value
        .Height = Range("A1").Value
    End With
End If
End Sub

 

[jrv326s]

Thank you so much PCG! The mechanism is working, although it is not what I would expect. I was thinking the value in the cell would effectively change the size property of the shape. For example, I'd think that if I put the value "1" in the cell "A1" then the value "1" would be inserted in the height and width of the "Size and Properties" for that shape. It does not. It's like those properties of the shape don't change and the value in "A1" is just a multiplier.

At any rate, I can work with this and I really appreciate the helping hand.

God bless.

Joshua

 

[pgc01]

The mechanism is working, although it is not what I would expect. I was thinking the value in the cell would effectively change the size property of the shape. For example, I'd think that if I put the value "1" in the cell "A1" then the value "1" would be inserted in the height and width of the "Size and Properties" for that shape. It does not. It's like those properties of the shape don't change and the value in "A1" is just a multiplier.

Hi Joshua

I'm glad it helped.

The value in A1 is indeed the value that will be in the height and width properties in the shape. If you check the help for those properties you'll see that these properties are measured in points.

What you see in the "Size and Properties" form is the values converted to whatever the measurement units you use in your country. For ex. if in your regional settings you use centimeters, then if you set A1 to 28 you'll get approximately 1 cm.

 

[jrv326s]

Working like a charm...except...

The use case is that these are circles on a US map showing how many resources we have in a given location. When I increase the size, the shape increases with the top left of the shape staying in the original position. This effectively moves the center of my circle off it's original location, defeating my purpose. I need to somehow either lock the center of the shape or increment movement of the shape up and to the left to compensate for the increased Height and Width. Clear as mud?

 

[pgc01]

Hi

In that case, in addition to the values of the .Width and the .Height properties, set also the values of the properties .Top and .Left. This allows you to control both the size and the position of the shape.

 

[AdriaanD]

Hi<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
I am all so struggling with this problem, I am really new with Excel and I need to create a model for a water tank according to a specific value in a cell! now I have tried your method but I just can't seem to make it work.<o></o>
All it needs to do is resize a Rectangle (but stay positioned in the same place ,only resize upwards to a max ) when I change the value in the specific cell!<o></o>
Can you please help me!!!<o></o>
<o></o>
Regards Adriaan<o></o>

 

[sirexcel]

I know that there are probably different ways to do this, but maybe you can do something like this;

VB:

With Cells
.WrapText = True
.EntireColumn.AutoFit
.EntireRow.AutoFit
End With

Is there a specific height or width that you want? How many columns will you be using? The excel formulas might differ a bit based on your size and column amount.

 

[sumanpanda]

Hi, i am doing similar exercise. i am wondering if i have to show 100rectangles with respect to 100 cell data, then do i have to enter the vba code for each rectangle ?

If anyone has any shortcut, then it would help a lot.

Thanks

 

[Perpa]

sumanpanda,

Greg Truby did a nice bit of code to 'find-all-rectangles', see the link below, post #5:

http://www.mrexcel.com/forum/excel-...l-basic-applications-find-all-rectangles.html

Hope this helps.
Perpa