excel - 如果 excel-vba 无模式用户窗体窗口重新获得焦点，会触发哪个事件？

Question

我在 Excel VBA 项目中有一个无模式用户窗体。用户表单通过单击电子表格上的按钮来加载(如果相关，则不是 active-x 按钮)。由于无模式，用户可以使用 Excel 甚至其他应用程序，而不是切换回表单窗口。 我需要一个在表单窗口再次成为事件窗口时触发的事件。我想 UserForm_Activate应该完成这项工作，但它没有(UserForm_GotFocus 也没有，但用户表单没有 GotFocus 事件？)。如果用户切换回无模式用户窗体(或者如果没有:是否有任何已知的解决方法)，是否会触发任何事件？或者我这里有一些奇怪的错误和Activate应该开火吗？

这是我用于测试目的的所有代码:

' standard module:

Sub BUTTON_FormLoad()
    ' associated as macro triggered by button click on a sheet
    UserForm1.Show vbModeless
End Sub


' UserForm1:

Private Sub UserForm_Activate()
    ' does not fire if focus comes back
    Debug.Print "Activated"
End Sub

Private Sub UserForm_GotFocus()
    ' does not fire if focus comes back
    ' wrong code - no GotFocus event for userforms?
    Debug.Print "Focussed"
End Sub

Private Sub UserForm_Click()
    ' only fires if clicked *inside* form
    ' does not fire eg if user clicks top of form window
    Debug.Print "Clicked"
End Sub

在哪里可以找到用户窗体事件的文档？它不在'UserForm object上' 页面。

Answer 1

当您在应用程序和无模式用户窗体之间切换时，Activate 事件不会触发。这是设计使然。

就像我在评论中提到的

You can achieve what you want by subclassing the userform and trapping the worksheet events but it very messy.

这是一个非常基本的示例。示例文件可以从Here下载

先读我:

1. 这只是一个基本示例。请在测试之前关闭所有 Excel 文件。
2. 如果用户直接单击用户表单上的控件，并且您也想在那里运行激活代码，那么您也必须处理该问题。
3. 一旦您满意，就可以修改它以满足您的需要。

将代码放入模块

Option Explicit

Private Declare Function CallWindowProc Lib "user32" Alias "CallWindowProcA" _
(ByVal lpPrevWndFunc As Long, ByVal hwnd As Long, ByVal msg As Long, _
ByVal wParam As Long, ByVal lParam As Long) As Long

Private Declare Function SetWindowLong& Lib "user32" Alias "SetWindowLongA" _
(ByVal hwnd As Long, ByVal nIndex As Long, ByVal dwNewLong As Long)

Private Const GWL_WNDPROC = (-4)
Private WinProcOld As Long
Private Const WM_NCLBUTTONDOWN = &HA1

Public formWasDeactivated As Boolean

'~~> Launch the form
Sub LaunchMyForm()
    Dim frm As New UserForm1
    frm.Show vbModeless
End Sub

'~~> Hooking the Title bar in case user clicks on the title bar
'~~> to activate the form
Public Function WinProc(ByVal hwnd As Long, ByVal wMsg As Long, ByVal wParam As Long, ByVal lParam As Long) As Long
    If wMsg = WM_NCLBUTTONDOWN Then
        '~~> Ignoring unnecessary clicks to the title bar
        '~~> by checking if the form was deactivated
        If formWasDeactivated = True Then
            formWasDeactivated = False
            MsgBox "Form Activated"
        End If
    End If

    WinProc = CallWindowProc(WinProcOld&, hwnd&, wMsg&, wParam&, lParam&)
End Function

'~~> Subclass the form
Sub SubClassUserform(hwnd As Long)
    WinProcOld& = SetWindowLong(hwnd, GWL_WNDPROC, AddressOf WinProc)
End Sub

Sub UnSubClassUserform(hwnd As Long)
    SetWindowLong hwnd, GWL_WNDPROC, WinProcOld&
    WinProcOld& = 0
End Sub

创建用户表单。我们将其命名为Userform1。在表单中添加命令按钮。我们将其命名为 CommandButton1

将代码放入用户表单

Option Explicit

Private Declare Function FindWindow Lib "user32.dll" _
Alias "FindWindowA" (ByVal lpClassName As String, _
ByVal lpWindowName As String) As Long

Dim hwnd As Long

Private Sub UserForm_Initialize()
    hwnd = FindWindow(vbNullString, Me.Caption)
    SubClassUserform hwnd
End Sub

'~~> Userform Click event
Private Sub UserForm_Click()
    '~~> Ignoring unnecessary clicks
    '~~> by checking if the form was deactivated
    If formWasDeactivated = True Then
        formWasDeactivated = False
        MsgBox "Form Activated"
    End If
End Sub

'~~> Unload the form
Private Sub CommandButton1_Click()
    '~~> In case hwnd gets reset for whatever reason.
    hwnd = FindWindow(vbNullString, Me.Caption)
    UnSubClassUserform hwnd

    Unload Me
End Sub

将此代码放入工作簿代码区域

Option Explicit

'~~> Checking if the form was deactivated
'~~> Add more events if you want

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    formWasDeactivated = True
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    formWasDeactivated = True
End Sub

请随时添加更多工作簿事件。我只使用了 Workbook_SheetActivate 和 Workbook_SheetSelectionChange

最后在工作表中添加一个表单按钮，并将宏 LaunchMyForm 分配给它。我们就完成了

实际行动