Keeping Userforms on top of SDI windows in Excel 2013 and up

Pages in this article

I have provided a demo file with the code shown below.

Code explanation

After having used the solution that involved making the userform the stay always on top of all Windows I decided this isn't the best solution after all. The problem with this solution rears its ugly head when you show a messagebox from the userforms code: the messagebox will appear behind the form!

The new solution listed here changes the parent window of the userform using a few API calls.

To make this portable, I created a class module with the code that does the heavy lifting.

In a class called cFormOnTop, add this code:

Option Explicit

'Object variable to trigger application events
Private WithEvents XLApp As Excel.Application

#If VBA7 Then
    Dim mXLHwnd As LongPtr    'Excel's window handle
    Dim mhwndForm As LongPtr  'The userform's window handle
    Private Declare PtrSafe Function FindWindowA Lib "user32" (ByVal lpClassName As String, ByVal lpWindowName As String) As LongPtr
    #If Win64 Then
        Private Declare PtrSafe Function SetWindowLongA Lib "user32" Alias "SetWindowLongPtrA" (ByVal hwnd As LongPtr, ByVal nIndex As Long, ByVal dwNewLong As LongPtr) As LongPtr
    #Else
        Private Declare PtrSafe Function SetWindowLongA Lib "user32" (ByVal hwnd As LongPtr, ByVal nIndex As Long, ByVal dwNewLong As LongPtr) As LongPtr
    #End If
    Private Declare PtrSafe Function SetForegroundWindow Lib "user32" (ByVal hwnd As LongPtr) As Long
#Else
    Dim mXLHwnd As Long    'Excel's window handle
    Dim mhwndForm As Long  'The userform's window handle
    Private Declare Function FindWindowA Lib "user32" (ByVal lpClassName As String, ByVal lpWindowName As String) As Long
    Private Declare Function SetWindowLongA Lib "user32" (ByVal hwnd As Long, ByVal nIndex As Long, ByVal dwNewLong As Long) As Long
    Private Declare Function SetForegroundWindow Lib "user32" (ByVal hwnd As Long) As Long
#End If

Const GWL_HWNDPARENT As Long = -8

Private moTheUserform As Object

Public Sub InitializeMe()
    If Val(Application.Version) >= 15 Then        'Only makes sense on Excel 2013 and up
        Set XLApp = Application
    End If
End Sub

Private Sub Class_Terminate()
    Set XLApp = Nothing
    Set moTheUserform = Nothing
End Sub

Private Sub XLApp_WindowActivate(ByVal Wb As Workbook, ByVal Wn As Window)
    If Val(Application.Version) >= 15 And mhwndForm <> 0 Then  'Basear o form na janela ativa do Excel.
        mXLHwnd = Application.hwnd    'Always get because in Excel 15 SDI each wb has its window with different handle.
        SetWindowLongA mhwndForm, GWL_HWNDPARENT, mXLHwnd
        SetForegroundWindow mhwndForm
    End If
End Sub

Private Sub XLApp_WindowResize(ByVal Wb As Workbook, ByVal Wn As Window)
    If Not moTheUserform.Visible Then moTheUserform.Show vbModeless
End Sub

Private Sub XLApp_WorkbookBeforeClose(ByVal Wb As Workbook, Cancel As Boolean)
    SetWindowLongA mhwndForm, GWL_HWNDPARENT, 0&
End Sub

Public Property Set TheUserform(ByVal oNewValue As Object)
    Set moTheUserform = oNewValue
    mhwndForm = FindWindowA("ThunderDFrame", moTheUserform.Caption)
End Property

After adding this class to your project, this is all code you need to add to your userform (works in Excel 2000-2021, 365, 32 and 64 bit):

Private mclsFormOnTop As cFormOnTop

Private Sub UserForm_Initialize()
    Set mclsFormOnTop = New cFormOnTop
    Set mclsFormOnTop.TheUserform = Me
    mclsFormOnTop.InitializeMe
End Sub

Conclusion

With the change from MDI to SDI, Excel 2013 has broken some solutions that depend on userforms staying on top of the Excel window regardless which workbook is the active workbook. The code demonstrated in this article shows you one way to overcome this limitation.

Previous solution: keep userform on top

The previous (incorrect) solution is shown below for completeness' sake.

One way around this problem is by setting the userform to be always on top using some Windows API calls.

The problem can be solved rather simple, by using some code that calls a couple of Windows API functions. All code could go inside the userform's code window. Unfortunately, because I chose to make the form topmost, we must handle the fact that another application might become the foreground window. For example: you are running Excel with the userform showing and then you open Word. In the simple case, the userform will stay on top of Word, which is not what we want.

So I opted for a solution which is more complex because it

handles multiple userforms,
hides all userforms when another application becomes the active application.

The sample file has these VBA objects:

The VBA Editor showing the sample file's VBA Project tree.

Object name

Description of the object

ufWorkbooks

A userform to test the code

modShowForm

Code to show the form

modTopMost

Code that handles hiding and showing of the form when Excel looses focus

clsForms

A class used by modTopMost to hold the form properties needed by that module

clsTopMost

Code to make the form topmost.

Code needed in the form

Since we're using a class module that does the heavy lifting, the code that is needed in the form is straightforward:

Variable declaration for the class's instance (in the declaration section of the form, at the top of its module):

Dim mcTopMost As clsTopMost
Instantiate a class instance, pass the form to it and make the form topmost (put this in a routine that is called during initialisation of the form):

    If Val(Application.Version) >= 15 Then
        'Only makes sense on Excel 2013 and up
        Set mcTopMost = New clsTopMost
        Set mcTopMost.Form = Me
        mcTopMost.MakeTopMost
        AddForm Me
    End If
To enable hiding of the form when Excel is no longer the foremost window, we pass the form to a routine in modTopMost:

AddForm Me

The clsTopMost class

The code in clsTopMost is not very complex, its most important part is a number of API function declarations and the proper way to call them to change the userform so it is "always on top".

Option Explicit

#If VBA7 Then
    Dim mhwndForm As LongPtr                                 'The userform's window handle
    Private Declare PtrSafe Function FindWindow32 Lib "USER32" Alias "FindWindowA" (ByVal lpClassName As String, _
                                                                                    ByVal lpWindowName As String) As LongPtr
    Private Declare PtrSafe Sub SetWindowPos Lib "USER32" (ByVal hwnd As LongPtr, ByVal hWndInsertAfter As LongPtr, _
                                                           ByVal X As Long, ByVal Y As Long, ByVal cx As Long, _
                                                           ByVal cy As Long, ByVal wFlags As Long)
    Private Const HWND_TOPMOST As LongPtr = -1
    Private Const HWND_NOTOPMOST As LongPtr = -2
#Else
    Dim mhwndForm As Long                                 'The userform's window handle
    Private Declare Function FindWindow32 Lib "USER32" Alias "FindWindowA" (ByVal lpClassName As String, _
                                                                            ByVal lpWindowName As String) As Long
    Private Declare Sub SetWindowPos Lib "USER32" (ByVal hwnd As Long, ByVal hWndInsertAfter As Long, _
                                                   ByVal X As Long, ByVal Y As Long, ByVal cx As Long, _
                                                   ByVal cy As Long, ByVal wFlags As Long)
    Private Const HWND_TOPMOST As Long = -1
    Private Const HWND_NOTOPMOST As Long = -2
#End If

Private Const SWP_NOSIZE As Long = &H1
Private Const SWP_NOMOVE As Long = &H2
Private Const SWP_NOACTIVATE As Long = &H10
Private Const SWP_SHOWWINDOW As Long = &H40

Private moForm As Object

Public Sub MakeTopMost()
#If VBA7 Then
    Dim lngParm As LongPtr
#Else
    Dim lngParm As Long
#End If
    mhwndForm = FindWindow32("ThunderDFrame", moForm.Caption)
    lngParm = IIf(mhwndForm, HWND_TOPMOST, HWND_NOTOPMOST)
    SetWindowPos mhwndForm, lngParm, 0, 0, 0, 0, (SWP_NOACTIVATE Or SWP_SHOWWINDOW Or SWP_NOMOVE Or SWP_NOSIZE)
End Sub

Private Sub Class_Terminate()
    Set moForm = Nothing
End Sub

Public Property Get Form() As Object
    Set Form = moForm
End Property

Public Property Set Form(oForm As Object)
    Set moForm = oForm
End Property

Public Property Get hwnd() As Long
    hwnd = mhwndForm
End Property

Note that I used conditional compilation in this code, so you can plug it into a workbook that might be used in older Excel versions without compile errors.

The module modTopMost

Now this is where things become a bit more complex; I need a way to find out whether or not Excel is the foreground window, or perhaps any of the userforms currently shown from Excel. All of the code in modTopMost follows below...

Option Explicit

'Handles Keeping modeless forms on top of Excel

#If VBA7 Then
    Dim mXLHwnd As LongPtr 'Excel's window handle
    Declare PtrSafe Function FindWindow32 Lib "USER32" Alias "FindWindowA" (ByVal lpClassName As String, _
                                                                            ByVal lpWindowName As String) As LongPtr
    Declare PtrSafe Function GetForegroundWindow Lib "user32.dll" () As LongPtr
#Else
    Dim mXLHwnd As Long 'Excel's window handle
    Declare Function FindWindow32 Lib "USER32" Alias "FindWindowA" (ByVal lpClassName As String, _
                                                                    ByVal lpWindowName As String) As Long
    Declare Function GetForegroundWindow Lib "user32.dll" () As Long
#End If

Dim mcForms As Collection

Dim mdNextTime As Double

Public Sub AddForm(oForm As Object)
    Dim cForm As clsForms
    If mcForms Is Nothing Then
        Set mcForms = New Collection
    End If
    Set cForm = New clsForms
    cForm.hwnd = FindWindow32("ThunderDFrame", oForm.Caption)
    Set cForm.Form = oForm
    mcForms.Add cForm
    Application.OnTime Now, "HandleFormHideUnHide"
End Sub

Public Sub RemoveForm(oForm2Remove As Object)
    Dim cForm As clsForms
    Dim lIndex As Long
    If Not mcForms Is Nothing Then
        On Error Resume Next
        For lIndex = mcForms.Count To 1 Step -1
            If mcForms(lIndex).Caption = oForm2Remove.Form.Caption Then
                'If this errors, we arrive here and should remove that form because its object was lost
                'if it doesn't error, we remove the form because the captions are the same
                mcForms.Remove lIndex
            End If
        Next
    End If
End Sub

Public Sub HandleFormHideUnHide()
    Dim oForm As Object
    Dim lIndex As Long
    Dim lHwndForeGround As LongPtr
    Dim bShow As Boolean
    If mcForms Is Nothing Then Exit Sub
    mXLHwnd = FindWindow32("XLMAIN", Application.Caption)
    If mXLHwnd = GetForegroundWindow Then
        bShow = True
    Else
        bShow = False
        For lIndex = 1 To mcForms.Count
            If GetForegroundWindow = mcForms(lIndex).hwnd Then
                bShow = True
                Exit For
            End If
        Next
    End If

    HideOrShow bShow
    mdNextTime = Now + TimeValue("00:00:01")
    Application.OnTime mdNextTime, "HandleFormHideUnHide"
End Sub

Sub Unschedule()
    On Error Resume Next
    Application.OnTime mdNextTime, "HandleFormHideUnHide", , False
    Set mcForms = Nothing
End Sub

Private Sub HideOrShow(bShow As Boolean)
    Dim lIndex As Long
    On Error Resume Next
    For lIndex = mcForms.Count To 1 Step -1
        Err.Clear
        If bShow Then
            mcForms(lIndex).Form.Show vbModeless
        Else
            mcForms(lIndex).Form.Hide
        End If
        If Err.Number <> 0 Then
            mcForms.Remove lIndex
        End If
    Next
End Sub

The routines in this module are described below:

AddForm

Adds a userform to the list of forms to "watch".

RemoveForm

Removes a form from the list.

HandleFormHideUnHide

A routine that is called every second which checks whether Excel or one of its userforms is on top and acts accordingly.

Unschedule

Cancels the timed routine when the last userform is removed from memory or when the workbook is closed.

HideOrShow

Hides or displays all userforms.

The class clsForms

This class is used to be able to get the window handles of the userforms easily, used from modTopMost.

The code in the class is:

Option Explicit

Private msCaption As String

Private moForm As Object

#If VBA7 Then
    Dim mlHwnd As LongPtr
#Else
    Dim mlHwnd As Long
#End If

Private Sub Class_Terminate()
    Set moForm = Nothing
End Sub

Public Property Get Caption() As String
    Caption = msCaption
End Property

Public Property Let Caption(sCaption As String)
    msCaption = sCaption
End Property

#If VBA7 Then
Public Property Get hwnd() As LongPtr
#Else
Public Property Get hwnd() As Long
#End If
    hwnd = mlHwnd
End Property

#If VBA7 Then
Public Property Let hwnd(lHwnd As LongPtr)
#Else
Public Property Let hwnd(lHwnd As Long)
#End If
    mlHwnd = lHwnd
End Property

Public Property Get Form() As Object
    Set Form = moForm
End Property

Public Property Set Form(oForm As Object)
    Set moForm = oForm
End Property

Comments

Showing last 8 comments of 104 in total (Show All Comments):

Comment by: Paul Gundersen (7-2-2023 13:20:00) deeplink to this comment

This post is very helpful... It is getting me a lot closer to where I need my code to be. My question has to do with the location of my form. When the form loads, it is being positioned in the top left corner of my windows screen. I need the form to be positioned against the center of the calling form. How would I do this with the code that you provided.

By the way, this is the o-n-l-y code example of all the examples I looked at that solves this problem. Thank you for your efforts.

Comment by: Jan Karel Pieterse (8-2-2023 10:17:00) deeplink to this comment

Hi Paul,

This behaviour is controlled by the StartUpPosition property of the userform.
To try, open a blank workbook and insert a userform with one label control. Then add this code to a normal module and run it:

Sub foobar()
    Dim u As UserForm1
    Dim ct As Long
    For ct = 0 To 3
        Set u = New UserForm1
        u.StartUpPosition = ct
        u.Label1.Caption = "StartupPosition set to: " & ct & vbNewLine & "Close form to see next"
        u.Show
    Next
End Sub

Comment by: Hans Troost (15-3-2023 21:09:00) deeplink to this comment

Dear Jan Karel,

Nice and informative article, but it seemed I misunderstood it, I'm afraid I missed the point...
I was - and am - in need for a userform ALWAYS on top of everything else (others apps etc.).
So tried your code on top of this page, expecting that this should do the trick. But: I was wrong, or even worse did something wrong:

The Userform came in front of the open Edge-window but behind the Explorer window, so between them.

Can you please help me a bit further with this?

Please find my code below: an automatically (Worksheet_Open() ) popping up userform. Just for testing now, for a lot off applications.

I will provide you with 2 imho illustrative screenshots via mail on your contact page. I'm not sure you appreciate this, it is for clarity purposes, but... I'll do.

Code in ThisWorkbook:

Private Sub Workbook_Open()
    With Application
        .Visible = False
        .WindowState = xlMinimized
        .Run "'" & ThisWorkbook.Name & "'!Test"
        .Visible = True
        .WindowState = xlNormal
    End With
End Sub

Code in normal module:

Code in the Userform frmTest

Private mclsFormOnTop As cFormOnTop
Private MsgStr As String

Private Sub btnOK_Click()
    Unload Me
End Sub

Private Sub btnShowMessage_Click()
    MsgBox MsgStr, vbSystemModal
End Sub

Private Sub UserForm_Initialize()
    Set mclsFormOnTop = New cFormOnTop
    Set mclsFormOnTop.TheUserform = Me
    mclsFormOnTop.InitializeMe
    #If VBA7 Then
        MsgStr = "VBA7 = True, "
    #Else
        MsgStr = "VBA7 = False, "
    #End If
    #If Win64 Then
        MsgStr = MsgStr & "Win64 = True"
    #Else
        MsgStr = MsgStr & "Win64 = False"
    #End If
End Sub

Class module code:
Just a copy of your code above, 1st from top of page

Kind regards,
Hans Troost

Comment by: Jan Karel Pieterse (17-3-2023 10:34:00) deeplink to this comment

Hi Hans,

There is a way to make a userform the top-most window of all windows, see this thread on mrExcel:
https://www.mrexcel.com/board/threads/userform-always-on-top.386643/

Comment by: Rick Hartt (29-3-2023 19:57:00) deeplink to this comment

Will you give me permission to modify cFormOnTop to work with Word applications?

Comment by: Jan Karel Pieterse (29-3-2023 21:01:00) deeplink to this comment

Hi Rick,

Sure, go ahead!

Comment by: Mike Berry (31-5-2023 23:54:00) deeplink to this comment

You are the man; thanks for a great piece of code which I use all over the place

Is there a reason that the Class_Terminate() Function is Private?

I found it necessary to change it to Public on a Project of mine to List all a Form's Controls

Comment by: Jan Karel Pieterse (1-6-2023 10:04:00) deeplink to this comment

Hi Mike,

Not sure I understand why you would need that event to be public.

The Terminate event of a class is private by default (try inserting a new class, click the top-left drop-down and select the class entry, it will insert the default class_Initialize event as a private event. If you then click the right-hand drop-down and select Terminate it inserts the terminate event as a private event.

There usually is no need to access a class' terminate event from outside of the class.

Have a question, comment or suggestion? Then please use this form.

If your question is not directly related to this web page, but rather a more general "How do I do this" Excel question, then I advise you to ask your question here: www.eileenslounge.com.