PDA

View Full Version : Solved: Use of AddShape to add circle



Onetrack
04-29-2010, 09:41 AM
Hi all, I'm trying to add a circle with the following parameters:

1. The circle is centred in the middle of the cell
2. The circle diameter is set by the numerical value entered into the cell

triggered by Worksheet_Change

[Ideally there would be an option to set the circle colour and line thickness also]

Excel 2003, WinXP

Thanks for your input.
JohnS

p45cal
04-29-2010, 01:56 PM
Play with the following:Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If IsNumeric(.Value) And .Value > 0 Then
Set TheCircle = ActiveSheet.Shapes.AddShape(msoShapeOval, .Left + .Width / 2 - .Value / 2, .Top + .Height / 2 - .Value / 2, .Value, .Value)
' Optional stuff:
' With TheCircle
' .LockAspectRatio = msoTrue
' With .Line
' .Weight = 3.5
' .ForeColor.SchemeColor = 10
' .BackColor.RGB = RGB(255, 255, 255)
' End With
' With .Fill
' .Solid
' .ForeColor.SchemeColor = 43
' .Transparency = 0.69
' End With
' End With
End If
End With
End Sub

Onetrack
04-30-2010, 01:18 AM
p45cal - that is great code. Thanks very much for that.
Sorry, but I can't see how to set this to SOLVED...