get range coordinates

[zz]

hello everyone:

Is there a way to obtain a range coordinates?

i need to get the coordinates of the selected range to place a shape in the
uppermost corner of the range and display it as a small tooltip with a "mini
rounded shape popup".


hope you can help me, i know you will.

thanks in advantage

 

[Gary Brown]

Selection.Address

--
HTH,
Gary Brown
(e-mail address removed)
If this post was helpful to you, please select ''YES'' at the bottom of the
post.

 

[Dave Peterson]

dim myRng as range
set myrng = activesheet.range("b1:C99")
msgbox myrng.topleftcell.address

But you may want to look at Insert|Comment or even Data|Validation.

 

[zz]

Sorry, i think i didn't explained myself:

the coordinates i need are those that indicates the placement of a shape

if i do this


shape("the_shape").top=250

i'm telling the system to move the shape to the 250 X coordinate

what i want to do is with the selected range is read the X coord. and the
Y coord. of the uppermost corner of the range.

i mean , reading the distance in pixels from the beginning of the range to
the opposite side.

this way i can show my "nice little rounded message" in such a fashion that
doesn't cover the data.

i'm ussing the message to display additional info about the selected data
with a previous calculation.

 

[Dave Peterson]

Maybe you can steal something from this sample:

Option Explicit
Sub testme01()
Dim myRng As Range
Dim myShape As Shape

With Worksheets("sheet1")
Set myRng = .Range("b9:c12")
End With

With myRng
Set myShape = .Parent.Shapes.AddShape _
(Type:=msoShapeRoundedRectangle, _
Top:=.Top, Left:=.Left, _
Width:=.Width, Height:=.Height)
End With

With myShape
.Name = "Note_" & myRng.Address(0, 0)
With .Fill
.Visible = msoTrue
.Solid
.ForeColor.SchemeColor = 42
.Transparency = 0
End With
With .Line
.Style = msoLineSingle
.DashStyle = msoLineSolid
.Weight = 0.75
.Visible = True
.ForeColor.SchemeColor = 64
.BackColor.RGB = RGB(255, 255, 255)
End With

With .TextFrame.Characters
.Text = "Some note here"
With .Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
End With
End With
End Sub

 

[zz]

great!!! still have some workaround pending but this snip will do exactly as
i wanted




Option Explicit

Sub testme01_snagged()

Dim myRng As Range
Dim myShape As Shape

With Worksheets("sheet1")
Set myRng = Selection
End With

With myRng
Set myShape =
..Parent.Shapes.AddShape(Type:=msoShapeRoundedRectangle, Top:=.Top - 2, _
Left:=.Left + .Width, Height:=200, Width:=250)

End With

End Sub



thanks sooooo much Dave, this also gave some new cool ideas ...