vba add shape on click

[hwkeyser]

Hello all,

I have an excel sheet which requires donuts being drawn all over it on a regular basis.

I'm hoping to write a macro that for a certain range on a spreadsheet, for arguments sake lets call it A1: I10, upon clicking or double-clicking within the cells, vba adds a donut originating at the middle of that cell.

I can imagine it uses the addShape command-- beyond that I don't have a clue where to get started, but if someone can get the shape(s) to appear, i can easily modify it to my needs from there.

so, in short-

• Click a cell within the range A1:I10

• addShape msoShapeDonut (originating aligned to the center of the cell)

• (optional) Will not circle blank cells

any help would be appreciated,

Henry

 

[p45cal]

Add this this to the sheet in question's code module:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Set xxx = Intersect(Range("A1:I10"), Target)
If Not xxx Is Nothing Then
    If Not IsEmpty(Target) Then
        Cancel = True
        mySize = 10
        Set myShape = Me.Shapes.AddShape(msoShapeDonut, Target.Left + Target.Width / 2 - mySize / 2, Target.Top + Target.Height / 2 - mySize / 2, mySize, mySize)
        myShape.LockAspectRatio = msoTrue
        myShape.ScaleWidth 2, msoFalse, msoScaleFromMiddle
    End If
End If
End Sub

It works on a double-click, not a single click - sorry.
(Excel 2010)

 

[hwkeyser]

This is pretty close to what i need- i just cant seem to find the attribute to lessen the gauge of the donut-- i'm looking for a nearly hollow circle.

Can you help with that too?

 

[p45cal]

add
myShape.Adjustments.Item(1) = 0.02
or use an oval instead of a donut with the fill invisible. Then change the line thickness.

 

[hwkeyser]

perfection!

Thanks P45cal!