Dynamic drawing shape

[aaronrm]

Does anybody if there is a way to have a square the changes sizes based
on given imput cells? I want it to work the same way a dynamic graph
does but I didn't seen anything in the graphing section that would
work.

thanks

 

[Dave Peterson]

I put a Rectangle from the Drawing toolbar on a worksheet. I called that
rectangle "rectangle 1".

Then I used A1 and B1 to control its size (it won't always be a square, but you
said cells (plural)).

Then I used an event macro that resized the rectangle based on the values in
A1:B1.

Right click on the worksheet tab and select view code. Paste this in the code
window:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim myRect As Rectangle

If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("a1:b1")) Is Nothing Then Exit Sub

Set myRect = Me.Rectangles("rectangle 1")

With myRect
.Width = Me.Range("a1").Value
.Height = Me.Range("b1").Value
End With

End Sub

 

[Bernard Liengme]

This is great but the rectangle changes size only when a number is TYPED
into one of the cell.
If the cells contain formulas (or are attached to controls) nothing happens
when their values change. Is it possible to do this?
thanks

 

[Dave Peterson]

Using worksheet_calculate should work with formulas.

Option Explicit
Private Sub Worksheet_Calculate()
Dim myRect As Rectangle
Set myRect = Me.Rectangles("rectangle 1")
With myRect
.Width = Me.Range("a1").Value
.Height = Me.Range("b1").Value
End With
End Sub

If I put scrollbars (say) from the Forms toolbar, I'd just assign a macro to
those scrollbars.

Option Explicit
Sub testme()
Dim myRect As Rectangle
Dim myScrollBar As ScrollBar

Set myScrollBar = ActiveSheet.ScrollBars(Application.Caller)
Set myRect = ActiveSheet.Rectangles("rectangle 1")

Select Case LCase(myScrollBar.Name)
Case Is = "scroll bar 1"
myRect.Width = myScrollBar.Value
Case Else
myRect.Height = myScrollBar.Value
End Select
End Sub

(I used scrollbars named: "Scroll Bar 1" and "Scroll Bar 2".)

 

[Bernard Liengme]

Many thanks

 

[aaronrm]

This recomendation worked great. Thanks

 

[aaronrm]

I have been working with the above code and one thing that seems off,
is that if the numbers in cell A1 and B1 are the same, shouldn't the
drawing be a perfect square? Maybe I am missing something.

Thanks

 

[Dave Peterson]

For my pc, it looks very close to a square. In fact, when I rotated it 90
degrees, it looked like it matched perfectly.

I'm not sure if it's a windows screen resolution difference or what. But wait
until you print it. I've seen posts that say that printing squares/circles come
out as non-square and more oval shaped.