UDF Drawing Shape Not Re-Positioning

[xl@lf]

Hello,

I created two (2) User Defined Equations (UDF). The second UDF
changes the size of a circle and repositions it with reference to a
circle from the first UDF. When a user changes the value of cell, the
diameter of the inside circle will change and should re-position it to
always touch the bottom of the outside circle. However, when the user
changes diameter, the position will not update until the cell (with
the UDF) is double clicked + enter, or ctrl+alt+f9 is pressed. I’ve
tried Application.Volatile with no luck. Any ideas would be much
appreciated. Thanks!



Function Casing_ID_Circle(name As String, Diameter)

Dim centerx As Single
Dim centery As Single
Dim Crcle As Shape
Dim SDiameter As Single

On Error GoTo SizeCircleErr

SDiameter = Diameter

Set Crcle = ActiveSheet.Shapes(name)
With Crcle
centerx = .Left + (.Width / 2)
centery = .Top + (.Height / 2)
.Width = Diameter
.Height = Diameter
.Left = centerx - (.Width / 2)
.Top = centery - (.Height / 2)
End With
SizeCircle = SDiameter

Exit Function

SizeCircleErr:
SizeCircle = CVErr(xlErrRef)
Exit Function

End Function



Function SizeCircle_TRSV(name As String, Diameter)

Dim centerx As Single
Dim centery As Single
Dim Crcle As Shape
Dim SDiameter As Single

On Error GoTo SizeCircleErr

SDiameterTRSV = Diameter

Set CrcleTRSV = ActiveSheet.Shapes(name)
With CrcleTRSV
centerx = ActiveSheet.Shapes("Casing_ID_Circle").Left +
ActiveSheet.Shapes("Casing_ID_Circle").Width / 2
centery = ActiveSheet.Shapes("Casing_ID_Circle").Top +
ActiveSheet.Shapes("Casing_ID_Circle").Height - .Height / 2
.Width = Diameter
.Height = Diameter
.Left = centerx - (.Width / 2)
.Top = centery - (.Height / 2)
End With
SizeCircle_TRSV = SDiameter

Exit Function

SizeCircleErr:
SizeCircle_TRSV = CVErr(xlErrRef)
Exit Function

End Function

 

[ker_01]

kl@lf:

I believe the issue is caused by using the height of Shape2 in a calculation
before you actually reset it to the new value.

I believe your problem will be solved if you re-order your lines of code and
set the Shape2 height above the first time you use it, e.g.:

With CrcleTRSV
.Width = Diameter
.Height = Diameter
centerx = ActiveSheet.Shapes("Casing_ID_Circle").Left +
ActiveSheet.Shapes("Casing_ID_Circle").Width / 2
centery = ActiveSheet.Shapes("Casing_ID_Circle").Top +
ActiveSheet.Shapes("Casing_ID_Circle").Height - .Height / 2

(it is this last line where you use .height in your calculation)

HTH,
Keith

 

[xl@lf]

That was it, thanks Keith!!!!! I spent about 6 hours on this problem
and it was driving me nuts, I can't believe it's such a simple
solution.


Have a great weekend,

Brian