Does RangeFromPoint Method return ranges?
 

Sefano,

Actually, it will return a range. Run this on on empty workbook - I think
100 and 200 will be enough to get your coordinates onto the worksheet:

Set myRFP = ActiveWindow.RangeFromPoint(100, 200)
If Not myRFP Is Nothing Then
MsgBox "Hello from " & myRFP.Address
End If

In actual use, you would need to check to see if myRFP was a range or a
shape...

HTH,
Bernie
MS Excel MVP

"Stefano Gatto" wrote in message
...
The help on the RangeFromPoint Method of XL 2000 says:

Returns the Shape or Range object that is positioned at the specified pair
of screen coordinates. If there isn't a shape located at the specified
coordinates, this method returns Nothing.

Can it return the range object or not? it looks like not (even if it
states it does)

Does RangeFromPoint Method return ranges?
 

You just have to remember that the X and Y coordinates are (if I understand
correctly), for the entire application window, not just for the
spreadsheet... so, this:

?TypeName(ActiveWindow.RangeFromPoint(1,1))
Nothing

returns Nothing because most likely, the pixel (1, 1) is part of the caption
of the application, or at least, of the menu bar.

--
Regards

Juan Pablo González

"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Sefano,

Actually, it will return a range. Run this on on empty workbook - I think
100 and 200 will be enough to get your coordinates onto the worksheet:

Set myRFP = ActiveWindow.RangeFromPoint(100, 200)
If Not myRFP Is Nothing Then
MsgBox "Hello from " & myRFP.Address
End If

In actual use, you would need to check to see if myRFP was a range or a
shape...

HTH,
Bernie
MS Excel MVP

"Stefano Gatto" wrote in message
...
The help on the RangeFromPoint Method of XL 2000 says:

Returns the Shape or Range object that is positioned at the specified
pair
of screen coordinates. If there isn't a shape located at the specified
coordinates, this method returns Nothing.

Can it return the range object or not? it looks like not (even if it
states it does)

Does RangeFromPoint Method return ranges?
 

Thanks guys this is very clear. I tried the VBA code and 100,200 were right
on the worksheet.

I am actually looking for the best method to find the cell on which a button
(or a rectangle shape) has its top left corner.

The thing is that I have dozens of buttons I reproduce by Filling Down a
range (ctrl-d) and I don't want to write different code for each button. So
I want the Sub to identify the button (which can be done with the Caller
property of Application) and its Top and Left properties:

x=thisworkbook.worksheets(1).buttons(application.c aller).left
y=thisworkbook.worksheets(1).buttons(application.c aller).top

Then, in order to find the cell located on the right of the button I wanted
a method to find the one-cell range containing the (x,y) coordinate or
pixel. Unfortunately and despite your kind explanations it does not help me
as these two scales are apparently offset one from each other.

Do you have something else to suggest instead of RangeFromPoint?

Thanks - Stefano




"Juan Pablo González" wrote in message
...
You just have to remember that the X and Y coordinates are (if I
understand
correctly), for the entire application window, not just for the
spreadsheet... so, this:

?TypeName(ActiveWindow.RangeFromPoint(1,1))
Nothing

returns Nothing because most likely, the pixel (1, 1) is part of the
caption
of the application, or at least, of the menu bar.

--
Regards

Juan Pablo González

"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Sefano,

Actually, it will return a range. Run this on on empty workbook - I
think
100 and 200 will be enough to get your coordinates onto the worksheet:

Set myRFP = ActiveWindow.RangeFromPoint(100, 200)
If Not myRFP Is Nothing Then
MsgBox "Hello from " & myRFP.Address
End If

In actual use, you would need to check to see if myRFP was a range or a
shape...

HTH,
Bernie
MS Excel MVP

"Stefano Gatto" wrote in message
...
The help on the RangeFromPoint Method of XL 2000 says:

Returns the Shape or Range object that is positioned at the specified
pair
of screen coordinates. If there isn't a shape located at the specified
coordinates, this method returns Nothing.

Can it return the range object or not? it looks like not (even if it
states it does)

Does RangeFromPoint Method return ranges?
 

I am actually looking for the best method to find the cell on which a button
(or a rectangle shape) has its top left corner.

Shapes and buttons have a TopLeftCell property designed for just such a purpose.


Regards,
Vic Eldridge

Does RangeFromPoint Method return ranges?
 

Thanks Vic, this is what I was looking for.

Stefano

PS: I started by searching the XL2K help but wasn't able to find it so I
reverted to this newsgroup.




"Vic Eldridge" wrote in message
m...
I am actually looking for the best method to find the cell on which a
button
(or a rectangle shape) has its top left corner.

Shapes and buttons have a TopLeftCell property designed for just such a
purpose.


Regards,
Vic Eldridge