Finally the penny drops ... thanks for sticking with me on this one!
This is what I'm now doing and it seems to work beautifully:
1) Call GetCursorPos to find mouse position (returned as pixels - I
think)
2) Get X (left) and Y (top) coordinates of cell A1 which are returned
in points
3) Convert cell coordinates to screen pixels using
ActiveWindow.PointsToPixelsX/Y
4) Subtract cell coordinates from mouse position coordinates to get
position relative to the sheet in pixels (zoom=100%)
5) Convert back to points and divide by Window Zoom
I need to tidy the whole thing up but once I'm fully happy with it
I'll post back in case anyone else is interested.
Thanks again for your help.
Andrew
On 9 Feb, 18:34, "Peter T" <peter_t@discussions wrote:
You don't need to be concerned with working out the distance to (say) the
top-left corner of A1 by taking into account QAT dim's, header sizes, is the
workbook window maximized or not, etc. But there's no need, as I mentioned
PointsToScreenPixelsX does it for you, albeit you have to factor in the
other adjustments I mentioned. *If anything PointsToScreenPixelsX works
better in 2007 as it now works with Panes.
If you get stuck I'll put a demo together and forward.
Regards,
Peter T
"Andrew" wrote in message
...
I think the problem is knowing the amount of space taken up by the
ribbon and QAT at the top of the window. *GetCursorPostion will return
coordinates relative to the window but shape objects are placed
relative to the worksheet area (0,0 at the top of cell A1). *Although
the conversion from points to pixels is a necessary step I don't think
it's enough. *Am I mis-interpreting you? *I'm not clear how I could
get the relative screen position of a cell using
PointsToScreenPixelsX?
Thanks again,
Andrew
On 8 Feb, 20:07, "Peter T" <peter_t@discussions wrote:
Well, we wouldn't want anything remotely "budget" in Excel would we!
You can get the relative screen position of any cell with
PointsToScreenPixelsX & 'Y. Apart from the mouse coordinates, which I
assume you already know how to get, you'll need to factor in
'points-per-pixel' (typically 0.75) and Zoom.
It gets a bit more complicated if there are multiple panes (freeze panes)
but PointsToScreenPixelsX/Y now works with the 'Pane' object, assuming you
know which pane you'll be dealing with, if applicable.
Only other thing you need to figure for your needs is how to trap the
mouse
click. Easiest would be in the selection event, which might mean you need
to
use WithEvents to trap the sheet events (unless of course your app is only
geared to work in a given workbook).
Regards,
Peter T
"Andrew" wrote in message
....
Indeed - if I use the selected cell coordinates the inserted picture/
shape can be almost as much as the width of the cell away from the
point where the user really wanted the insert to occur. It's not the
end of the world but does appear a bit "budget".
Thanks
On 8 Feb, 16:05, "Peter T" <peter_t@discussions wrote:
Either I'm missing something or you've already got the information you
need.
You say user clicks on the worksheet so presumably that activates a
cell -
what more do you need than that. IOW why do you need the mouse
coordinates
when you've already got the cell coordinates, which is what you will be
using to position your inserted shape.
Or, wondering, do you want the exact spot within some cell rather than
say
the top/left cell coordinate.
Regards,
Peter T
"Andrew" wrote in message
....
Hi Peter,
Thanks for for the reply.
I have a series of buttons displayed on the Ribbon which should insert
a picture at a location selected by the user (by clicking on the
worksheet). I need to be able to translate the mouse location to the
appropriate sheet coordinates in order to insert the picture at the
right spot.
Thanks again,
Andrew
On 8 Feb, 15:21, "Peter T" <peter_t@discussions wrote:
That approach doesn't work in 2007+ as embedded charts are not windows
in
they way they were in previous versions.
There are other ways of relating mouse coordinates to a cell address,
or
a
cell position to screen coordinates depending on the overall
objective.
Regards,
Peter T
"Andrew" wrote in message
...
Some years ago I remember reading an approach for determining the
mouse position within a sheet. I believe this used the GetCursorPos
API to find the "absolute" mouse position relative to the window,
then
inserted a dummy chart object in cell A1, set a handle to this and
then compared the coordinates of the chart to the mouse position to
determine its relative position on the sheet. I've spent about an
hour now searching for this code without success - does anyone have
a
copy they could re-post?
Thanks a lot,
Andrew- Hide quoted text -
- Show quoted text -- Hide quoted text -
- Show quoted text -- Hide quoted text -
- Show quoted text -- Hide quoted text -
- Show quoted text -