ThreeWave Setting Focus To The Worksheet

This page describes how to keep focus on the worksheet when displaying a modeless form.
ShortFadeBar

Setting Focus To The Worksheet

When you show a userform, either modally or modelessly, the input focus is on that form and within the form on the first control. There may be times that when displaying a userform that you don't want the form to receive focus. For example, you might have a information only form that you want to display, but leave focus on the worksheet so that when the user starts to type, the input goes to the worksheet not the form. You can accomplish this with a few simple Windows API calls. This technique will work only on user forms that are shown modelessly (e.g,. UserForm.Show vbModeless.

In the userform's code module, paste the following API function declarations at the top of the module, outside of and before any Sub or Function or Property procedures: 

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

Now, paste in the following procedure. This procedure sets focus to the worksheet. 

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

If you always want to set focus to the worksheet, just call this function from the user form's Activate event: 

Private Sub UserForm_Activate()
    SetSheetFocus
End Sub

If you want the ability to indicate to the form whether the worksheet should receive focus, first create a public variable in the form code module's declarations section (outside of and before any procedure). 

Public SetFocusToWorksheet As Boolean

Now, change the form's Activate event to: 

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

This will cause the form to set focus back to the worksheet only if the SetFocusToWorksheet variable is True. You set the value of this variable immediately before calling the form's Show method. You'll need to Load the user form first. The follow code illustrates how to show the form such that it will set focus on the worksheet by setting the SetFocusToWorksheet variable to True. 

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

As noted earlier, you must show the form modelessly by using the vbModeless parameter of the Show method.

ShortFadeBar
LastUpdate This page last updated: 18-March-2010.

-->