Excel VBA Challenge: Creating a callout with exact location

[naddad]

Hey all,

I have a table (duh, excel!). I have created a small script: If you
right click any cell of that table, an entry "Insert Comment" in the
context menu appears. I want the action of that entry to create a
callout (in AutoShapes/callouts) whose tip points to that selected
cell. Does any guru know how to proceed about doing that in VBA?

 

[Bob Phillips]

One way

Sub AddCallout()
Dim x, y
Dim shp As Shape
With ActiveCell
x = .Left
y = .Top
Set shp = ActiveSheet.Shapes.AddShape( _
msoShapeRoundedRectangularCallout, _
Left:=x, Top:=y - 120, Width:=100, Height:=100)
End With
End Sub

You will need to play with the numbers to get the shape that you want. The
connection between the bubble and the cell seems to tale 20% of its height,
extra to its height.

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

 

[Guest]

This should give you a start...

Public Sub AddCallout()
Dim shp As Shape

Set shp = ActiveSheet.Shapes.AddShape(msoShapeRoundedRectangularCallout, _
ActiveCell.Left + ActiveCell.Width, ActiveCell.Top - 35, 75, 35)
shp.DrawingObject.Text = "Tada!"
End Sub

 

[naddad]

Thanks, got it