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:
The Function is then added to a module:
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 Function
What 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