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

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

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 -

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 -

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 -

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 -

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 -

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 -

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 -

I think you may be missing one or two things (not sure), in essence the
position of the mouse pointer in points from the top-left corner of the
sheet is

CursorPixelsX - PointsToScreenPixelsX(0) * ppp * zoom%

where CursorPixelsX is returned from 'GetCursorPos' and ppp refers to
'Points-per-Pixel' typically 0.75 but confirmed with a few API calls.

There are one or two other things that might also need to be taken into
account. Having had another look at some old stuff I should be able to post
a simple demo here later today, rather than something needing to be wrapped
in a workbook.

Regards,
Peter T


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

Put the following into ThisWorkbook and Normal modules as indicated.
Hold Ctrl and Right-click to center "TheSun" under the cursor

''' ThisWorkbook module

Option Explicit
Private Declare Function GetKeyState32 Lib "user32" _
Alias "GetKeyState" (ByVal vKey As Integer) As Integer


Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, _
ByVal Target As Range, Cancel As Boolean)
Dim bCtrl As Long

' Right-click and hold Ctrl <<<

' is Ctrl pressed
bCtrl = GetKeyState32(vbKeyControl) < 0

If bCtrl Then
Cancel = True ' prevent the rt-click menu
TestCursorToPoints Sh
End If

End Sub

''' end ThisWorkbook module

''' code in normal module

Option Explicit
''' pmbthornton at gmail dot com

' re points per pixel
Private Const LOGPIXELSX As Long = 88&
Private Const POINTS_PER_INCH As Long = 72&
Private Declare Function GetDC Lib "user32" ( _
ByVal hwnd As Long) As Long
Private Declare Function ReleaseDC Lib "user32" ( _
ByVal hwnd As Long, ByVal hdc As Long) As Long
Private Declare Function GetDeviceCaps Lib "gdi32" ( _
ByVal hdc As Long, ByVal nIndex As Long) As Long
Private Declare Function GetDesktopWindow Lib "user32" () As Long

' re cursor position
Private Type POINTAPI
X As Long
Y As Long
End Type
Private Declare Function GetCursorPos Lib "user32.dll" ( _
ByRef lpPoint As POINTAPI) As Long

Private mPPP As Single ' points per pixel

Function CursorToPoints(X As Single, Y As Single) As Long
Dim x0 As Single, y0 As Single
Dim zm As Single
Dim rngCursor As Range
Dim pta As POINTAPI

On Error GoTo errH

If mPPP = 0 Then getPPP

Call GetCursorPos(pta)

With ActiveWindow

If .Panes.Count = 1 Then
x0 = .PointsToScreenPixelsX(0)
y0 = .PointsToScreenPixelsY(0)

ElseIf Val(Application.Version) = 12 Then

With .Panes(.Panes.Count)
x0 = .PointsToScreenPixelsX(0)
y0 = .PointsToScreenPixelsY(0)
End With
Else
Err.Raise 10100, , _
"To do: cater for Freeze Panes in 2000-2003"
End If

If x0 = 0 And y0 = 0 Then
Err.Raise 10200, , _
"At least part of the worksheet must be in view"
End If

zm = 100 / .Zoom

X = (pta.X - x0) * mPPP * zm
Y = (pta.Y - y0) * mPPP * zm

On Error Resume Next
' attempt to return the cell under the cursor
' btw, if only need to return the cell under the mouse
' this is all that's required
Set rngCursor = .RangeFromPoint(pta.X, pta.Y)
On Error GoTo errH

End With

If Not rngCursor Is Nothing Then

If rngCursor.Address = ActiveCell.Address Then
CursorToPoints = 2 ' mouse over activecell
Else
CursorToPoints = 1 ' mouse not over activecell
End If

ElseIf X < 0 Or Y < 0 Then
CursorToPoints = 0 ' mouse above or to left of visible cells
Else
CursorToPoints = -1 ' mouse to right or below visible cells
End If

Exit Function

errH:
MsgBox Err.Description, , "CursorToPoints"

End Function

Sub getPPP()
' get Points / Pixel
' typically ppp is 72/96 = 0.75 in systems with Normal Fonts
Dim hWin As Long
Dim dcDT As Long
Dim nDPI As Long

hWin = GetDesktopWindow
dcDT = GetDC(hWin)
nDPI = GetDeviceCaps(dcDT, LOGPIXELSX)
ReleaseDC hWin, dcDT
mPPP = POINTS_PER_INCH / nDPI

End Sub

''''''' Test code '''''''

Sub test()
TestCursorToPoints ActiveSheet
End Sub

Sub TestCursorToPoints(ws As Worksheet)
Dim bVis As Boolean, bCenter As Boolean
Dim res As Long
Dim X As Single, Y As Single

res = CursorToPoints(X, Y)

bVis = CBool(res)
bCenter = True

MoveTheSun ws, X, Y, bVis, bCenter

End Sub

Sub MoveTheSun(ws As Worksheet, X As Single, Y As Single, _
bVis As Boolean, bCenter As Boolean)
Dim nL As Single, nT As Single
Dim shp As Shape
Const cW As Single = 24, cH As Single = 24
Const cSUN As String = "TheSun"

nL = X
nT = Y
If bCenter Then
nL = nL - (cW / 2)
nT = nT - (cH / 2)
End If

On Error Resume Next
Set shp = ActiveSheet.Shapes(cSUN)
On Error GoTo 0

If shp Is Nothing Then
Set shp = ws.Shapes.AddShape(msoShapeSun, nL, nT, cW, cH)
shp.Fill.ForeColor.RGB = RGB(255, 240, 140)
shp.Line.ForeColor.RGB = RGB(255, 180, 0)
shp.Name = cSUN

Else
With shp
.Left = nL
.Top = nT
.Width = cW
.Height = cH
.Visible = bVis
End With
End If

End Sub


Regrds,
Peter T

Embarrassingly I failed to compute that top of A1 is just 0!! Too
busy thinking about everything else.

BTW, there's a bracket missing in your expression below though.
Should be:
(CursorPixelsX - PointsToScreenPixelsX(0)) * ppp * zoom%
since it's the distance from top left of A1 to the cursor which
changes with zoom etc. (I know you know this since it is what you
have in your code that you posted later).

Thanks!

On 10 Feb, 13:28, "Peter T" <peter_t@discussions wrote:
I think you may be missing one or two things (not sure), in essence the
position of the mouse pointer in points from the top-left corner of the
sheet is

CursorPixelsX - PointsToScreenPixelsX(0) * ppp * zoom%

where CursorPixelsX is returned from 'GetCursorPos' and ppp refers to
'Points-per-Pixel' typically 0.75 but confirmed with a few API calls.

There are one or two other things that might also need to be taken into
account. Having had another look at some old stuff I should be able to post
a simple demo here later today, rather than something needing to be wrapped
in a workbook.

Regards,
Peter T

"Andrew" wrote in message

...
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 -- Hide quoted text -

- Show quoted text -

Thanks for posting this. I hadn't realised that 'freeze panes' would
work like this. My app at the moment is for XL07 so it doesn't cause
a problem.

All the best,
Andrew


On 10 Feb, 16:11, "Peter T" <peter_t@discussions wrote:
Put the following into ThisWorkbook and Normal modules as indicated.
Hold Ctrl and Right-click to center "TheSun" under the cursor

''' ThisWorkbook module

Option Explicit
Private Declare Function GetKeyState32 Lib "user32" _
* * Alias "GetKeyState" (ByVal vKey As Integer) As Integer

Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, _
* * * * * * * * * * * * * * ByVal Target As Range, Cancel As Boolean)
Dim bCtrl As Long

' Right-click and hold Ctrl <<<

* * ' is Ctrl pressed
* * bCtrl = GetKeyState32(vbKeyControl) < 0

* * If bCtrl Then
* * * * Cancel = True ' prevent the rt-click menu
* * * * TestCursorToPoints Sh
* * End If

End Sub

''' end ThisWorkbook module

''' code in normal module

Option Explicit
''' pmbthornton at gmail dot com

' re points per pixel
Private Const LOGPIXELSX As Long = 88&
Private Const POINTS_PER_INCH As Long = 72&
Private Declare Function GetDC Lib "user32" ( _
* * * * * * * * * * ByVal hwnd As Long) As Long
Private Declare Function ReleaseDC Lib "user32" ( _
* * * * * * * * * * ByVal hwnd As Long, ByVal hdc As Long) As Long
Private Declare Function GetDeviceCaps Lib "gdi32" ( _
* * * * * * * * * * ByVal hdc As Long, ByVal nIndex As Long) As Long
Private Declare Function GetDesktopWindow Lib "user32" () As Long

' re cursor position
Private Type POINTAPI
* * X As Long
* * Y As Long
End Type
Private Declare Function GetCursorPos Lib "user32.dll" ( _
* * * * * * * * * * ByRef lpPoint As POINTAPI) As Long

Private mPPP As Single * *' points per pixel

Function CursorToPoints(X As Single, Y As Single) As Long
Dim x0 As Single, y0 As Single
Dim zm As Single
Dim rngCursor As Range
Dim pta As POINTAPI

* * On Error GoTo errH

* * If mPPP = 0 Then getPPP

* * Call GetCursorPos(pta)

* * With ActiveWindow

* * * * If .Panes.Count = 1 Then
* * * * * * x0 = .PointsToScreenPixelsX(0)
* * * * * * y0 = .PointsToScreenPixelsY(0)

* * * * ElseIf Val(Application.Version) = 12 Then

* * * * * * With .Panes(.Panes.Count)
* * * * * * * * x0 = .PointsToScreenPixelsX(0)
* * * * * * * * y0 = .PointsToScreenPixelsY(0)
* * * * * * End With
* * * * Else
* * * * * * Err.Raise 10100, , _
* * * * * * * * "To do: cater for Freeze Panes in 2000-2003"
* * * * End If

* * * * If x0 = 0 And y0 = 0 Then
* * * * * * Err.Raise 10200, , _
* * * * * * * * "At least part of the worksheet must be in view"
* * * * End If

* * * * zm = 100 / .Zoom

* * * * X = (pta.X - x0) * mPPP * zm
* * * * Y = (pta.Y - y0) * mPPP * zm

* * * * On Error Resume Next
* * * * ' attempt to return the cell under the cursor
* * * * ' btw, if only need to return the cell under the mouse
* * * * ' this is all that's required
* * * * Set rngCursor = .RangeFromPoint(pta.X, pta.Y)
* * * * On Error GoTo errH

* * End With

* * If Not rngCursor Is Nothing Then

* * * * If rngCursor.Address = ActiveCell.Address Then
* * * * * * CursorToPoints = 2 ' mouse over activecell
* * * * Else
* * * * * * CursorToPoints = 1 ' mouse not over activecell
* * * * End If

* * ElseIf X < 0 Or Y < 0 Then
* * * * CursorToPoints = 0 * * ' mouse above or to left of visible cells
* * Else
* * * * CursorToPoints = -1 * *' mouse to right or below visible cells
* * End If

* * Exit Function

errH:
* * MsgBox Err.Description, , "CursorToPoints"

End Function

Sub getPPP()
' get Points / Pixel
' typically ppp is 72/96 = 0.75 in systems with Normal Fonts
Dim hWin As Long
Dim dcDT As Long
Dim nDPI As Long

* * hWin = GetDesktopWindow
* * dcDT = GetDC(hWin)
* * nDPI = GetDeviceCaps(dcDT, LOGPIXELSX)
* * ReleaseDC hWin, dcDT
* * mPPP = POINTS_PER_INCH / nDPI

End Sub

''''''' Test code '''''''

Sub test()
* * TestCursorToPoints ActiveSheet
End Sub

Sub TestCursorToPoints(ws As Worksheet)
Dim bVis As Boolean, bCenter As Boolean
Dim res As Long
Dim X As Single, Y As Single

* * res = CursorToPoints(X, Y)

* * bVis = CBool(res)
* * bCenter = True

* * MoveTheSun ws, X, Y, bVis, bCenter

End Sub

Sub MoveTheSun(ws As Worksheet, X As Single, Y As Single, _
* * * * * * * * * * * * * *bVis As Boolean, bCenter As Boolean)
Dim nL As Single, nT As Single
Dim shp As Shape
Const cW As Single = 24, cH As Single = 24
Const cSUN As String = "TheSun"

* * nL = X
* * nT = Y
* * If bCenter Then
* * * * nL = nL - (cW / 2)
* * * * nT = nT - (cH / 2)
* * End If

* * On Error Resume Next
* * Set shp = ActiveSheet.Shapes(cSUN)
* * On Error GoTo 0

* * If shp Is Nothing Then
* * * * Set shp = ws.Shapes.AddShape(msoShapeSun, nL, nT, cW, cH)
* * * * shp.Fill.ForeColor.RGB = RGB(255, 240, 140)
* * * * shp.Line.ForeColor.RGB = RGB(255, 180, 0)
* * * * shp.Name = cSUN

* * Else
* * * * With shp
* * * * * * .Left = nL
* * * * * * .Top = nT
* * * * * * .Width = cW
* * * * * * .Height = cH
* * * * * * .Visible = bVis
* * * * End With
* * End If

End Sub

Regrds,
Peter T

Ah, the brackets, how observant. Aircode I'm afraid but they were there by
intention!

Trust the demo worked, but please advise if anything not quite right.

Regards,
Peter T


"Andrew" wrote in message
...
Embarrassingly I failed to compute that top of A1 is just 0!! Too
busy thinking about everything else.

BTW, there's a bracket missing in your expression below though.
Should be:
(CursorPixelsX - PointsToScreenPixelsX(0)) * ppp * zoom%
since it's the distance from top left of A1 to the cursor which
changes with zoom etc. (I know you know this since it is what you
have in your code that you posted later).

Thanks!

On 10 Feb, 13:28, "Peter T" <peter_t@discussions wrote:
I think you may be missing one or two things (not sure), in essence the
position of the mouse pointer in points from the top-left corner of the
sheet is

CursorPixelsX - PointsToScreenPixelsX(0) * ppp * zoom%

where CursorPixelsX is returned from 'GetCursorPos' and ppp refers to
'Points-per-Pixel' typically 0.75 but confirmed with a few API calls.

There are one or two other things that might also need to be taken into
account. Having had another look at some old stuff I should be able to
post
a simple demo here later today, rather than something needing to be
wrapped
in a workbook.

Regards,
Peter T

<snip