Most Valuable Professional


View Jan Karel Pieterse's profile on LinkedIn subscribe to rss feed
Subscribe in a reader

Subscribe to our mailing list

* indicates required

File crashes Excel!!

Save my workbook!
The best tool for salvaging problematic workbooks.

Trainings

Excel VBA Masterclass (English)
Excel VBA for Financials (Dutch)

Third party tools

Speed up your file

FastExcel
The best tool to optimise your Excel model!

Repair your file

Stellar Phoenix Excel Repair
Best tool to repair corrupt Excel sheets and objects
Home > English site > Articles > Keep Userform On Top > The code

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

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.

This is all code you need to add to your userform (works in Excel 2000-2013 32 and 64 bit):

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 Sub UserForm_Initialize()
    If Val(Application.Version) >= 15 Then        'Only makes sense on Excel 2013 and up
        Set XLApp = Application
        mhwndForm = FindWindowA("ThunderDFrame", Caption)
    End If
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 Me.Visible Then Me.Show vbModeless
End Sub

Private Sub XLApp_WorkbookBeforeClose(ByVal Wb As Workbook, Cancel As Boolean)
    SetWindowLongA mhwndForm, GWL_HWNDPARENT, 0&
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

The sample file has these VBA objects:

The VBA Editor showing the sample file's VBA Project tree.
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:

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

        Dim mcTopMost As clsTopMost

  2. 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
  3. 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 62 in total (Show All Comments):

 


Comment by: michael lalonde (6/24/2017 12:11:02 AM)

Thank you for posting this useful bit of code. I have a tool that heavily relies on the ability to switch between workbooks so this was very useful. One challenge however, I use Ctrl Tab extensively to switch between open workbooks. Upon implementation of this code, Ctrl-Tab works the first time but then the focus is the form and pressing Ctrl-Tab again, in the case of my form, switches tabs on the form instead of switching workbooks. Do you have a work around for this? Thank you again! ML

 


Comment by: Jan Karel Pieterse (6/26/2017 2:49:08 PM)

Hi Michael,

Thanks for letting me know.

You could add some code that re-activates the Excel window perhaps. There is plenty of code samples available that shows how to do that.

 


Comment by: Alvaro Diaz (7/3/2017 8:48:37 PM)

Hi, I have added your code to my user form and now it stays on top of every open window. However, the form itself does not behave properly any more. More precisely, I use RefEdit to select cells in the active worksheet, and once I select a cell I cannot close the form anymore or press the Ok or Cancel buttons.
Have you ever come across this? Any tips?
Thanks in advance.
Alvaro Diaz

 


Comment by: Jan Karel Pieterse (7/3/2017 10:21:30 PM)

Hi Alvaro,

Hmm. Haven't tried using a refedit with this. I hardly ever use it because it has some odd behaviour sometimes. I prefer to use a button which calls a function using Application.InputBox with the last argument being set to 8.

 


Comment by: Jessie Lopez (9/26/2017 11:03:26 PM)

Hello all, thank you very much for this information as it has saved me a ton of headaches. I have used the code sample provided by Dennis along with some of the changes posted and it has resolved the issues I was having, however, there is a slight problem. Lets say there is another application running like maybe IE. If user switches to IE and then back to excel the userform is no longer visible and the only way to get it back is by alt-tabbing to it. If the user alt-tabs to the form while IE is open it still shows up however the spreadsheet is no longer visible and if they click excel to show the spreadsheet the form of course goes away again. So basically they have to switch to excel and then alt-tab to the form while excel is active for it to work. I would like to make sure the form shows when switching back to excel. Hoping someone here can lend a hand, thanks again everyone.

 


Comment by: Jan Karel Pieterse (9/27/2017 10:29:22 AM)

Hi Jessie,

Sounds to me like an app-level event class is needed that has a Window_Activate event inside that brings the form back on top of the active workbook.
Not sure if that event will trigger however, you'll have to experiment I'm afraid :-)

 


Comment by: Jessie Lopez (9/27/2017 4:14:38 PM)

Hello,

Thank you for your comment. That's my problem actually, I have been experimenting and can't seem to find a solution haha!!! Was hoping someone much smarter than me could say "hey dummy just do this". I will keep trying and thanks again!!!

 


Comment by: Jan Karel Pieterse (9/27/2017 4:24:47 PM)

Hi Jessie,

I'm sorry not to have such a quick solution for you :-)

 


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.

Please enter your name (required):

Your e-mail address (optional but if you want me to respond it helps!; will not be shown, nor be used to send you unsolicited information):

Your request or comment:

To post VBA code in your comment, use [VB] tags, like this: [VB]Code goes here[/VB].