Excel VBA Challenge: Creating a callout with exact location

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

naddad - 24 Jan 2006 22:37 GMT

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?

Signature

naddad

Reply to this Message

Bob Phillips - 24 Jan 2006 23:20 GMT

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)

> Hey all,
>
[quoted text clipped - 9 lines]
> naddad's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=29817
> View this thread: http://www.excelforum.com/showthread.php?threadid=504682

Reply to this Message

Jim Thomlinson - 24 Jan 2006 23:37 GMT

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

Signature

HTH...

Jim Thomlinson

> Hey all,
>
[quoted text clipped - 3 lines]
> callout (in AutoShapes/callouts) whose tip points to that selected
> cell. Does any guru know how to proceed about doing that in VBA?

Reply to this Message

naddad - 24 Jan 2006 23:44 GMT

Thanks, got it :)

Signature

naddad

Reply to this Message