Excel VBA Challenge: Creating a callout with exact location

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?

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)

"naddad" <[email protected]> wrote in
message news:[email protected]...
>
> 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?
>
>
> --
> naddad
> ------------------------------------------------------------------------
> naddad's Profile:
http://www.excelforum.com/member.php...o&userid=29817
> View this thread: http://www.excelforum.com/showthread...hreadid=504682
>

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
--
HTH...

Jim Thomlinson


"naddad" wrote:

>
> 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?
>
>
> --
> naddad
> ------------------------------------------------------------------------
> naddad's Profile: http://www.excelforum.com/member.php...o&userid=29817
> View this thread: http://www.excelforum.com/showthread...hreadid=504682
>
>

Thanks, got it