Excel中显示窗体时保持输入焦点在工作表上

当你在Excel中显示一个窗体时，不管是模式窗体还是非模式窗体，输入焦点都会在这个弹出的窗体的第一个控件上，但有时你并不希望这个窗体获取输入焦点，如这个弹出窗体只是显示一些相关信息时，你希望输入焦点保持在工作表上，这样你可以继续方便地录入数据。要完成这一需求，只需调用几个简单的API函数(FindWindowEx与SendMessage)即可，注意的是必须使用非模式窗体(UserForm.Show vbModeless)。

'================================
' Excel中显示窗体时保持输入焦点在工作表上
'
' 
'================================
Private Declare Function FindWindowEx _
Lib "user32" Alias "FindWindowExA" ( _
    ByVal hWnd1 As Long, _
    ByVal hWnd2 As Long, _
    ByVal lpsz1 As String, _
    ByVal lpsz2 As String) As Long
Private Declare Function SendMessage _
Lib "user32" Alias "SendMessageA" ( _
    ByVal HWnd As Long, _
    ByVal wMsg As Long, _
    ByVal wParam As Long, _
    lParam As Any) As Long
Private Const WM_SETFOCUS = &H7

Private Sub SetSheetFocus()
    Dim HWND_XLDesk As Long
    Dim HWND_XLApp As Long
    Dim HWND_XLSheet As Long
    HWND_XLApp = Application.HWnd
    HWND_XLDesk = FindWindowEx(HWND_XLApp, 0&, "XLDESK", vbNullString)
    HWND_XLSheet = FindWindowEx(HWND_XLDesk, 0&, "EXCEL7", ActiveWindow.Caption)
    SendMessage HWND_XLSheet, WM_SETFOCUS, 0&, 0&
End Sub

Public SetFocusToWorksheet As Boolean

Private Sub UserForm_Activate()
    If SetFocusToWorksheet = True Then
        SetSheetFocus
    End If
End Sub

使用示例：

Sub TestSetFocusToWorksheet()
    Load UserForm1
    UserForm1.SetFocusToWorksheet = True
    UserForm1.Show vbModeless
End Sub

相关文件下载：Excel中显示窗体时保持输入焦点在工作表上示例 (2389)