RangeFromPoint problems

[[email protected]]

I am having trouble using RangeFromPoint. I seem to get errors if I
set the return from RangeFromPoint to a Range object or a Shape object.
I realize that RangeFromPoint will return Nothing if a shape is not
located at the specified coordinates, but what I pass to it are the
Left and Top values of the shape and I still get errors. Is there
something about using RangeFromPoint that is not documented?

Roy

[Dave Peterson]

I've never used .RangeFromPoint, but it sure looks like the rangefrompoint's
top/left and shape's top/left are based on different starting points.

If you look at VBA's help for RangeFromPoint, you'll see:

expression An expression that returns a Window object.

x Required Long. The value (in pixels) that represents the horizontal
distance from the left edge of the screen, starting at the top.

y Required Long. The value (in pixels) that represents the vertical distance
from the top of the screen, starting on the left.


And if you look at VBA's help for .addshape, you'll see:

Left, Top Required Single. The position (in points) of the upper-left corner of
the AutoShape's bounding box relative to the upper-left corner of the document.

I googled for .rangefrompoint and found this post:
http://groups.google.co.uk/group/mic...a2274d902fc826

(one line in your browser)

======
I put this in a general module:

Option Explicit
Declare Function GetCursorPos Lib "user32" (lpPoint As POINTAPI) As Long

Type POINTAPI
x As Long
y As Long
End Type

Then I added a label from the control toolbox toolbar (so I could use the
mouseover event) on a worksheet.

The I double clicked on that label and pasted this into the code window.

Option Explicit
Private Sub Label1_MouseMove(ByVal Button As Integer, _
ByVal Shift As Integer, ByVal x As Single, ByVal y As Single)

Dim MyRange As Object
Dim RetVal As Long
Dim pa As POINTAPI

With Application
.ScreenUpdating = False
With .Windows(1)
RetVal = GetCursorPos(pa)
Set MyRange = .RangeFromPoint(pa.x, pa.y)
Debug.Print pa.x & "--" & pa.y & vbLf & TypeName(MyRange) _
& vbLf & MyRange.Top & "--" & MyRange.Left & vbLf _
& "------"
End With
.ScreenUpdating = True
End With

End Sub

And I got this back:

388--478
OLEObject
204.75--213
------

Then I moved my excel application window.

And I got this:
429--434
OLEObject
204.75--213
------

This sure looks like it's made for web based stuff. If you're something for a
web application, good luck. If you're not, what are you trying to do?





[email protected] wrote:
>
> I am having trouble using RangeFromPoint. I seem to get errors if I
> set the return from RangeFromPoint to a Range object or a Shape object.
> I realize that RangeFromPoint will return Nothing if a shape is not
> located at the specified coordinates, but what I pass to it are the
> Left and Top values of the shape and I still get errors. Is there
> something about using RangeFromPoint that is not documented?
>
> Roy

--

Dave Peterson

[[email protected]]

Thanks for your reply. I will look at what you sent. Basically, we
are looking to implement some drag/drop capability by allowing the user
to move "special" cells we have defined as buttons around on the sheet.
We have figured out the drag and drop fairly easily, but the one piece
we need and what I was trying to use RangeFromPoint to figure out was
determining what cell the shape is over during the mouse drag (mouse
move) or drop (mouse up) operation. There will only be certain "legal"
places where the user can move these buttons to on the sheet and we'd
like to be able to know what cell the user is currently hovering over,
either during the move operation or the drop itself. These operations
give us an X and Y on the sheet. I was hoping RangeFromPoint was going
to do it, given the way the VBA documentation reads. Apparently, it is
either mis-documented or I am missing something here. If you have any
thoughts on this or know of any online resources that might help,
please feel free to point me in that direction. Thanks again. - Roy

[Dave Peterson]

I dropped a shape onto a worksheet.

I assigned this macro to that shape:

Option Explicit
Sub testme()

Dim myShape As Shape
Dim myRng As Range

With ActiveSheet
Set myShape = .Shapes(Application.Caller)
Set myRng = .Range(myShape.TopLeftCell, myShape.BottomRightCell)
'do whatever you need with that range
MsgBox myRng.Address
End With

End Sub

I got the address of the cells that were under that shape.

Maybe it'll give you an idea.

[email protected] wrote:
>
> Thanks for your reply. I will look at what you sent. Basically, we
> are looking to implement some drag/drop capability by allowing the user
> to move "special" cells we have defined as buttons around on the sheet.
> We have figured out the drag and drop fairly easily, but the one piece
> we need and what I was trying to use RangeFromPoint to figure out was
> determining what cell the shape is over during the mouse drag (mouse
> move) or drop (mouse up) operation. There will only be certain "legal"
> places where the user can move these buttons to on the sheet and we'd
> like to be able to know what cell the user is currently hovering over,
> either during the move operation or the drop itself. These operations
> give us an X and Y on the sheet. I was hoping RangeFromPoint was going
> to do it, given the way the VBA documentation reads. Apparently, it is
> either mis-documented or I am missing something here. If you have any
> thoughts on this or know of any online resources that might help,
> please feel free to point me in that direction. Thanks again. - Roy

--

Dave Peterson