As shown on the attached file forum.ozgrid.com/index.php?attachment/61865/, I need the diameters of four circles to be set by the values in four cells.
A thread on here from 2007 led me to put this Formula into D2. It sets the size of shape "Oval 14" based on a value in cell C2:
=udfSizeCircle("Oval 14",C2)
The Function is then added to a module:
Code
Option Explicit
Function udfSizeCircle(Name As String, Diameter)
'
' Diameter in cms
'
Dim sngCenterX As Single
Dim sngCenterY As Single
Dim shpCircle As Shape
Dim sngDiameter As Single
On Error GoTo SizeCircleErr
sngDiameter = Diameter
Set shpCircle = ActiveSheet.Shapes(Name)
With shpCircle
sngCenterX = .Left + (.Width / 2)
sngCenterY = .Top + (.Height / 2)
.Width = Application.CentimetersToPoints(sngDiameter)
.Height = Application.CentimetersToPoints(sngDiameter)
.Left = sngCenterX - (.Width / 2)
.Top = sngCenterY - (.Height / 2)
End With
udfSizeCircle = sngDiameter
Exit Function
SizeCircleErr:
udfSizeCircle = CVErr(xlErrRef)
Exit Function
End FunctionWhat I can't work out is how to "cycle" through multiple shapes so each one is sized by a different cell.
The file has a "static example" on the RHS showing what I want to achieve.
All suggestions received gratefully
Ochimus