vba - 如何获取工作表上光标的 (X,Y) 坐标？

Question

我希望能够创建一个图表，其左上角位于我的光标位置。那可能吗？我的鼠标的(X,Y)可以转换为范围格式吗？

Answer 1

嗯，据我所知，它并不完全内置，但我发现 this page这给出了对我有用的建议:

在模块中，将其放在顶部:

Private Declare Function GetCursorPos Lib "user32" (lpPoint As POINTAPI) As Long
Private Declare Function ScreenToClient Lib "user32" (ByVal hWnd As Long, _
    lpPoint As POINTAPI) As Long
    Private Type POINTAPI
    X As Long
    Y As Long
End Type

然后，为了让子例程获取 mouseX 和 mouseY，请将其放在下面的某个位置:

Function MouseX(Optional ByVal hWnd As Long) As Long
' Get mouse X coordinates in pixels
'
' If a window handle is passed, the result is relative to the client area
' of that window, otherwise the result is relative to the screen
    Dim lpPoint As POINTAPI
    Application.Volatile(false)
    GetCursorPos lpPoint
    If hWnd Then ScreenToClient hWnd, lpPoint
    MouseX = lpPoint.X
End Function

和

Function MouseY(Optional ByVal hWnd As Long) As Long
' Get mouse Y coordinates in pixels
'
' If a window handle is passed, the result is relative to the client area
' of that window, otherwise the result is relative to the screen

    Dim lpPoint As POINTAPI
    Application.Volatile(false)
    GetCursorPos lpPoint
    If hWnd Then ScreenToClient hWnd, lpPoint
    MouseY = lpPoint.Y
End Function

然后，在 Excel 中，如果您只需在单元格中输入 =mouseX()，当您按 ENTER 时，它将返回 mouseX 位置。与 =mouseY() 相同。

尝试了一下，我做到了:

Sub chart_Test()

    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.ChartType = xlLine
    ActiveSheet.Shapes("Chart 1").Top = MouseY()
    ActiveSheet.Shapes("Chart 1").Left = MouseX()

End Sub

并让它发挥作用。

编辑:请注意，我不像 VBA 中的其他东西那样擅长图表，因此在创建图表时，您需要编辑 .Shapes("Chart 1")。部分到您所在的任何图表名称/数字。或者迭代它们。