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?


--
naddad
------------------------------------------------------------------------
naddad's Profile: http://www.excelforum.com/member.php...o&userid=29817
View this thread: http://www.excelforum.com/showthread...hreadid=504682

Excel VBA Challenge: Creating a callout with exact location
 

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" wrote in
message ...

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

Excel VBA Challenge: Creating a callout with exact location
 

This should give you a start...

Public Sub AddCallout()
Dim shp As Shape

Set shp = ActiveSheet.Shapes.AddShape(msoShapeRoundedRectang ularCallout, _
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

Excel VBA Challenge: Creating a callout with exact location
 

Thanks, got it :)


--
naddad
------------------------------------------------------------------------
naddad's Profile: http://www.excelforum.com/member.php...o&userid=29817
View this thread: http://www.excelforum.com/showthread...hreadid=504682