Pearson Software Consulting

This page describes how to use the SetParent API function to control how UserForms are displayed.
ShortFadeBar

Normally, a modeless userform floats above, and independently of, the Excel application window and the worksheet windows. This means that when you move the application window or a sheet window, the form does not move with the window -- it stays in its original location on the screen, disconnected from the Excel windows. Broadly speaking, this is not desirable. It is my experience that the users expect the userform to move along with the application window or the sheet window.

DEFINITION: A modeless form, or a form displayed modelessly, is where the form remains visible but does not have focus, and you can edit the worksheet or work with menus and command bars while the form is visible. The "Find" dialog is an example of a modeless form. You can modify cell values while the form is visible on the screen. A modal form, or a form shown modally, is when the form has focus and you can access only items on the form while it is visible. You cannot access anything that is not on the form until the form is hidden or closed. The "Options" dialog is an example of a modal form. You must close this dialog before doing anything else.

There is no "move" event of any Excel object, so you cannot detect when the user moves a window. To overcome this situation, you can use a series of Windows API system calls to set the form as a "child" window to the worksheet window. When you do this, the form will move along with the application window and the sheet window.

Note: This code applies only to Excel 2000 (VBA6) and later, and only for forms that are shown modelessly. It will not work in Excel97 or earlier, and will not work with modal forms. You show a form modelessly with code like the following:

You can also show a form modelessly by setting the userform's ShowModal property to False.

Download an example workbook illustrating the code here.

All of the following code should be placed in the userform's code module. At the top of the module, outside of and before any procedure, paste the code shown below.

Private Declare Function SetParent Lib "user32" ( _
    ByVal hWndChild As Long, _
    ByVal hWndNewParent As Long) As Long

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

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 GetAncestor Lib "user32.dll" ( _
    ByVal hwnd As Long, _
    ByVal gaFlags As Long) As Long

Note that the function declarations above must be Private not Public . You can't have Public declarations in an object module, such as a Sheet, Form or Class module.

Then, use the userform's Initialize event to set the form as a child to the worksheet window:

Private Sub UserForm_Initialize()

Const C_VBA6_USERFORM_CLASSNAME = "ThunderDFrame"
Dim AppHWnd As Long
Dim DeskHWnd As Long
Dim WindowHWnd As Long
Dim MeHWnd As Long
Dim Res As Long

' Get the window handle of the main Excel application window.
' Note, in Excel 2002 and later, you can use "Application.HWnd"
' rather than "FindWindow("XLMAIN", Application.Caption)" to get the
' handle of the main application window. In Excel 2002 and later,
' uncomment the line
' AppHWnd = Application.HWnd
' and remove the call to
' FindWindow("XLMAIN", Application.Caption)
'
'<<<
' Excel 2002 and later only
'AppHWnd = Application.HWnd
'<<<

' The following line of code is not necessary if you are in Excel 2002 or later
' and you are using "AppHWnd = Application.HWnd" to get the window handle to
' the Excel Application window.
AppHWnd = FindWindow("XLMAIN", Application.Caption)

If AppHWnd > 0 Then
    ' get the window handle of the Excel desktop
    DeskHWnd = FindWindowEx(AppHWnd, 0&, "XLDESK", vbNullString)
    If DeskHWnd > 0 Then
        ' get the window handle of the ActiveWindow
        WindowHWnd = FindWindowEx(DeskHWnd, 0&, "EXCEL7", ActiveWindow.Caption)
        If WindowHWnd > 0 Then
            ' ok
        Else
            MsgBox "Unable to get the window handle of the ActiveWindow."
        End If
    Else
        MsgBox "Unable to get the window handle of the Excel Desktop."
    End If
Else
    MsgBox "Unable to get the window handle of the Excel Application."
End If

' get the window handle of the userform
MeHWnd = FindWindow(C_VBA6_USERFORM_CLASSNAME, Me.Caption)

If (MeHWnd > 0) And (WindowHWnd > 0) Then
     ' make the userform a child window of the ActiveWindow
     Res = SetParent (MeHWnd, WindowHWnd)
     If Res = 0 Then
         ''''''''''''''''''''
         ' an error occurred.
         ''''''''''''''''''''
         MsgBox "The call to SetParent failed."
     End If
End If

End Sub

The code above makes the form a child of the active worksheet window. That means that if you switch to another window the form will disappear and will reappear when you activate the original window. If you want the form to remain visible in all windows, but still move with the application window, use the following simplified UserForm_Initialize procedure to make the form a child window of the main application window.

Private Sub UserForm_Initialize()

    Const C_VBA6_USERFORM_CLASSNAME = "ThunderDFrame"
    Const GA_ROOTOWNER As Long = 3&

    Dim AppHWnd As Long
    Dim UserFormHWnd As Long
    Dim Res As Long
    ''''''''''''''''''''''''''''''
    ' Get the HWnd of the UserForm
    ''''''''''''''''''''''''''''''
    UserFormHWnd = FindWindow(C_VBA6_USERFORM_CLASSNAME, Me.Caption)
    If UserFormHWnd > 0 Then
        ''''''''''''''''''''''''
        ' Get the ROOTOWNER HWnd
        ''''''''''''''''''''''''
        AppHWnd = GetAncestor(UserFormHWnd, GA_ROOTOWNER)
        If AppHWnd > 0 Then
            '''''''''''''''''''''''''''''''''
            ' Call SetParent to make the form
            ' a child of the application.
            '''''''''''''''''''''''''''''''''
            Res = SetParent(UserFormHWnd, AppHWnd)
            If Res = 0 Then
                ''''''''''''''''''''
                ' An error occurred.
                ''''''''''''''''''''
                MsgBox "The call to SetParent failed."
            End If
        End If
    End If
End Sub

Note: This code applies only to Excel 2000 and above, and applies only to forms that are displayed modelessly: