Use mouse location to place autoshape via VBA



I'm have a worksheet with an imported picture. I'm using the mouse X/Y
coordinates to identify specific points on the picture, which will be used in
calculating the location of autoshapes that will be placed with a later macro.

To make sure that I'm headed in the right direction, I set up my code to
move a small autoshape to the mouse location when I click on the picture. I'm
getting reliable X/Y coordinates, but the autoshape is not ending up where
the mouse is. (autoshape top/left = pox.y and pos.x)

At this point, it seems likely that the mouse X/Y is actual (anywhere on the
screen or toolbars) whereas the autoshape is maybe offset from the top left
of the worksheet(?).

I won't be the only user of this worksheet, and different users may have
different numbers of toolbars/menu/etc. What is the most reliable way to
identify the relative start point for an autoshape placement, and work
backward to my actual mouse X/Y coordinates?

Thank you,

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

Global PasteNow
Public MyX
Public MyY

X As Long
Y As Long
End Type

Sub ShowPos()
PasteNow = False
Do Until (StopMe = True)
Dim lRetVal As Long
lRetVal = GetCursorPos(Pos)
Application.StatusBar = Pos.X & ", " & Pos.Y
Str1 = "1. " & Pos.X & ", " & Pos.Y
If PasteNow = True Then
UseCol = Chr(MyY + 65)
UseRow = MyX + 1
Sheet3.Range(UseCol & UseRow).Value = Pos.X
Sheet3.Range(UseCol & (UseRow + 10)).Value = Pos.Y
Debug.Print Str1 & " 2. " & Pos.X & ", " & Pos.Y
Sheet2.Shapes("Oval 2").Left = (Val(Pos.X))
Sheet2.Shapes("Oval 2").Top = (Val(Pos.Y) )
Debug.Print Sheet2.Shapes("Oval 2").Left & ", " &
Sheet2.Shapes("Oval 2").Top
PasteNow = False
End If
End Sub

Sub PictureClick()
End Sub

{userform 1 has two text boxes and a submit button; these are values to help
me insert the X/Y coordinates onto a second sheet for permanent storage)}


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question