Excel VBA如何检测用户移动应用窗口 实现UserForm位置同步对齐

实现思路 #

VBA本身没有提供直接监听Excel主窗口移动的原生事件，需要借助Windows API实现钩子（Hook）来捕获主窗口的位置变更、大小调整消息，触发回调后调整UserForm位置即可。

具体实现步骤 #

• 第一步：在标准模块顶部声明用到的API、常量和全局变量（已兼容32/64位Office）

#If VBA7 Then
    Declare PtrSafe Function SetWindowsHookEx Lib "user32" Alias "SetWindowsHookExA" (ByVal idHook As Long, ByVal lpfn As LongPtr, ByVal hmod As LongPtr, ByVal dwThreadId As Long) As LongPtr
    Declare PtrSafe Function CallNextHookEx Lib "user32" (ByVal hHook As LongPtr, ByVal nCode As Long, ByVal wParam As LongPtr, lParam As Any) As LongPtr
    Declare PtrSafe Function UnhookWindowsHookEx Lib "user32" (ByVal hHook As LongPtr) As Long
    Declare PtrSafe Function GetWindowRect Lib "user32" (ByVal hwnd As LongPtr, lpRect As RECT) As Long
    Type RECT
        Left As Long
        Top As Long
        Right As Long
        Bottom As Long
    End Type
    Dim hHook As LongPtr
    Public excelHwnd As LongPtr
#Else
    Declare Function SetWindowsHookEx Lib "user32" Alias "SetWindowsHookExA" (ByVal idHook As Long, ByVal lpfn As Long, ByVal hmod As Long, ByVal dwThreadId As Long) As Long
    Declare Function CallNextHookEx Lib "user32" (ByVal hHook As Long, ByVal nCode As Long, ByVal wParam As Long, lParam As Any) As Long
    Declare Function UnhookWindowsHookEx Lib "user32" (ByVal hHook As Long) As Long
    Declare Function GetWindowRect Lib "user32" (ByVal hwnd As Long, lpRect As RECT) As Long
    Type RECT
        Left As Long
        Top As Long
        Right As Long
        Bottom As Long
    End Type
    Dim hHook As Long
    Public excelHwnd As Long
#End If
Const WH_CALLWNDPROC = 4
Const WM_MOVE = &H3
Const WM_SIZE = &H5

• 第二步：在标准模块中编写钩子回调函数，捕获消息后同步UserForm位置

Public Function CallWndProc(ByVal nCode As Long, ByVal wParam As LongPtr, ByVal lParam As Long) As LongPtr
    Dim excelRect As RECT
    If nCode >= 0 Then
        ' 捕获Excel窗口移动、大小调整事件
        If wParam = WM_MOVE Or wParam = WM_SIZE Then
            GetWindowRect excelHwnd, excelRect
            ' 对齐右边缘，Top位置的偏移量可根据需求自定义
            UserForm1.Left = excelRect.Right - UserForm1.Width
            UserForm1.Top = excelRect.Top + 50
        End If
    End If
    CallWndProc = CallNextHookEx(hHook, nCode, wParam, ByVal lParam)
End Function

• 第三步：在UserForm初始化事件中启动钩子

Private Sub UserForm_Initialize()
    excelHwnd = Application.hwnd
    hHook = SetWindowsHookEx(WH_CALLWNDPROC, AddressOf CallWndProc, 0, Application.ThreadID)
End Sub

• 第四步：在UserForm关闭事件中卸载钩子，避免内存泄漏

Private Sub UserForm_Terminate()
    If hHook <> 0 Then
        UnhookWindowsHookEx hHook
        hHook = 0
    End If
End Sub

注意事项 #

• 所有API声明、全局变量和回调函数必须放在标准模块中，不能放在UserForm的代码模块里。
• 请将代码中的UserForm1替换为你实际使用的UserForm对象名称。
• 调试时不要直接强行中止代码运行，必须通过关闭UserForm触发Terminate事件卸载钩子，否则可能导致Excel崩溃。

内容的提问来源于stack exchange，提问作者VanDee