Search code examples
excelvbachartsuserformmouse-coordinates

Showing chart coordinates in userform using mousemove

I am trying to make userform that can show chart coordinates when i hover mouse over the chart

Here are some explanations:

-I have userform named "userform1" that has image named "image1"

-EDIT : the same userform has height (467.25),left (0), top(0),width(876), startupposition = 1-CenterOwner

-the same image has height (426),left (6), top(6),width(702)

-the same userform have 2 label named "label_x" and "label_y"

-the image will import chart with x value of (0 to 100) and y value of (100 to 200)

-the code below is to show coordinates of image1 when I hover mouse over

Private Sub Image1_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)

Dim coor As POINTAPI

GetCursorPos coor

UserForm1.Label_x.caption = " X : " & coor.X
UserForm1.Label_y.caption = " Y : " & coor.Y
End Sub

-here are the demonstration of current code the x and y value that shown are wrong

i have tried to make a manual calibration by asking user to click the top right and bottom left corner of the chart to calibrate the coordinates correctly

But i dont think it will be comfortable for user to do that

Is there a way to automatically calibrate chart coordinates? I think it have to do with userform position, image position, and screen resolution, but i do not know how

Thanks

Solution

  • I think i have figure it out how to do it it is still semi automatic method, I hope it can be a future reference for others

    I still hope for other method

    here how it is done

    -First we need to get the user screen resolution

    Declare PtrSafe Function GetSystemMetrics& Lib "User32" (ByVal nIndex&)
Sub ScreenResSize()

   Dim res_x As Long, res_y As Long

   res_x = GetSystemMetrics(0) ' width
   res_y = GetSystemMetrics(1) ' height

End Sub

    -second, we need to get 2 point of the chart manually, I take bottom left and top right of the chart, then use this calculation and sub below

    Private Sub Image1_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
'Semi automatic to show chart coordinate by : Efsion Andre

Dim coor As POINTAPI,xp1 As Double, xp2 As Double, yp1 As Double, yp2 As Double, xd1 As Double, xd2 As Double, yd1 As Double, yd2 As Double, xd As Double, yd As Double

GetCursorPos coor

xp1 = 280 'NEED MANUAL CALIBRATE BY PROGAMMER - BOTTOM LEFT OF CHART
xp2 = 1054 'NEED MANUAL CALIBRATE BY PROGAMMER - TOP RIGHT OF CHART
yp1 = 682 'NEED MANUAL CALIBRATE BY PROGAMMER - BOTTOM LEFT OF CHART
yp2 = 184 'NEED MANUAL CALIBRATE BY PROGAMMER - TOP RIGHT OF CHART

xp1 = (res_x - 1600) / 2 + xp1 'RECALCULATE BASED ON SCREEN RESOLUTION, MY SCREEN RESOLUTION IS 1600 X 900
xp2 = (res_x - 1600) / 2 + xp2 'RECALCULATE BASED ON SCREEN RESOLUTION, MY SCREEN RESOLUTION IS 1600 X 900
yp1 = (res_y - 900) / 2 + yp1 'RECALCULATE BASED ON SCREEN RESOLUTION, MY SCREEN RESOLUTION IS 1600 X 900
yp2 = (res_y - 900) / 2 + yp2 'RECALCULATE BASED ON SCREEN RESOLUTION, MY SCREEN RESOLUTION IS 1600 X 900

xd = (xd1 - xd2) / (xp1 - xp2) * (coor.X - xp2) + xd2 'CALIBRATION
yd = (yd1 - yd2) / (yp1 - yp2) * (coor.Y - yp2) + yd2 'CALIBRATION

userform1.Label_x.caption = " X : " & WorksheetFunction.RoundUp(xd, 2)
userform1.Label_y.caption = " Y : " & WorksheetFunction.RoundUp(yd, 2)

End Sub