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

All comments about this page:

Comment by: Chip Pearson (1-2-2013 21:59:24) deeplink to this comment

I think the conditional compilation directives are incorrect. You need to test for Win64 in addition to VBA7. VBA7 is true if you are in Office 2010 or later, either 32-bit or 64-bit Office. Win64 will be true only if you are in 64-bit Office. So, to get the correct version of the Declare statement, you need

#If VBA7 And Win64 Then
' Office 2010 or later, 64-bit
#Else
' Office 2007 or earlier, 32-bit.
#End If

Comment by: Jan Karel Pieterse (2-2-2013 15:03:22) deeplink to this comment

Hi Chip,

I've always doubted whether or not you are correct on this.

So far, the current conditional compilation appears to work on both 32 an64 bit Excel 2010/2013.

But I'd love to be put straight here!

Comment by: JTsang (18-2-2013 10:58:39) deeplink to this comment

Hi, the Application.Version is 15 for Excel 2013, isn't it?

Comment by: Jan Karel Pieterse (18-2-2013 11:55:38) deeplink to this comment

Hi JTsang,

Yes. Use Val(Application.Version) to make sure you do not run into issues with decimal separators and such.

Comment by: JTsang (18-2-2013 14:41:41) deeplink to this comment

Sorry, I guess what I meant to say is that it looks like there's a typo:-

If Val(Application.Version) >= 13 Then
'Only makes sense on Excel 2013 and up

Comment by: Jan Karel Pieterse (18-2-2013 16:58:23) deeplink to this comment

Hi JTsang,

Well spotted!
I'll correct the code and this page.

Comment by: Dutch Gemini (4-7-2013 12:17:15) deeplink to this comment

I tried the suggested solution [on an application that creates a modeless UserForm via XLAM] without success but found another way.

After a lot of trial and error I found out that the UserForm was always showing 'On Top', when the window that was active at the time Excel booted was clicked. This let me believe that the UserForm was not anymore associated to the instance of Excel [which would have kept it always on top] but to the window itself, and I was right.

After a lot of searches on usable Win32API's I bounced into GetAncestor() which confirmed that [handle of] the UserForm's owner was indeed "Application.Windows(1).Hwnd".

In my XLAM there is a hook to the Application's Events and in "WindowActivate" I was able to detect the window that has become active.

To make it all work I have:
- added a "Public Property" called "Hwnd" to the UserForm retrieving its own "Hwnd" with 'FindWindow("ThunderDFrame", Me.Caption)'
- in the Application's "MyApp_WindowActivate(ByVal Ww As Workbook, ByVal Wn As Window)" Event I issue a 'SetWindowLongPtr(MyUserForm.Hwnd, -8&, Wn.Hwnd)' to give the ownership to the active window
- in the same event procedure I issue a 'BringWindowToTop MyUserForm.Hwnd

I did multiple runs with and so far it is working as expected, each time I click another window the ownership of the modeless UserForm is changed to the active window and it will again show on top.

There is no need to create an extra class, 3 simple Win32API functions do the work for me. The only problem was finding the '-8&' value. It is hardly documented but I will try to come back with the URL to the explanation.

Dutch

Comment by: Jan Karel Pieterse (4-7-2013 17:27:39) deeplink to this comment

Hi Dutch Gemini,

Thanks!

Comment by: Dutch Gemini (4-7-2013 17:53:09) deeplink to this comment

In the mean time I discovered that "Application.Hwnd" always returns the handle to the "active" window, i.e. all versions of Excel tried so far (2002-2013). Hooking the UserForm to "Application.Hwnd" is therefore a safe harbour. Hence, there is no need to take over the Window.Hwnd (which does not even exist in 2010 and below).

I had to add a little bit of coding to make sure the UserForm disappeared together with its 'owner' when minimised (it could stick on screen) but the result is now really looking great, and behaving like on XL2010.

I will come back in August (after my well deserved holidays) with some sample code as long as it is not cluttering this page, or I send a sample workbook to you Karel if that's possible. Let me know with a PM.

Dutch

Comment by: Debbie Grebenc (6-9-2013 06:11:39) deeplink to this comment

I would love to get a sample of the solution proposed by Dutch Gemini. I have a modeless UserForm that I think could be fixed up to run in Excel 2013 with his method, and don't want to reinvent the wheel. Dutch, are you back from your well deserved holidays? Are you still willing to share?

Deb

Comment by: Jan Karel Pieterse (7-9-2013 16:25:55) deeplink to this comment

Hi Debbie,

Me too, but so far Dutch hasn't responded :-)

Comment by: Debbie Grebenc (9-9-2013 18:31:34) deeplink to this comment

I've got the modeless window in my Excel add-in working with Excel 2013 using this solution:

http://cpap.com.br/cd.asp?F=ModelessformOnTop002.zip

I think it might be the same solution that Dutch is proposing, because both do a FindWindow looking for "ThunderDFrame". Anyway, it worked well for me, even if it's not exactly what Dutch was suggesting.

Comment by: Jan Karel Pieterse (9-9-2013 18:35:31) deeplink to this comment

Hi Debbie,

Thanks for the link. I looked at that method and it is simple indeed.
The only disadvantage is that the userform "steals" back the focus when you click in Excel and then switch to a different workbook.

Comment by: Roman (17-12-2013 20:55:45) deeplink to this comment

Excuse me for this stupid question, but could I ask you what does symbol "#" do before if, else, end?

And one more - I suppose that your solution does not help in case the userform cannot be in modeless state - since it contains RefEdit control which is not compartible with modeless userforms. Is my guess right?

To be honest this sudden switch to SDI just before the very launch of my VBA project which strongly requires MDI at one stage now turns me to the utter despair. The ones who discarded elementary compartibility in such a violent way should be burning in hell.

Thank you.

Comment by: Jan Karel Pieterse (17-12-2013 21:13:49) deeplink to this comment

Hi Roman,

The # are for conditional compilation. VBA help explains how that works quite well.

Comment by: Pieter (22-1-2014 17:08:47) deeplink to this comment

Another basic question, is it true that the main window in EXCEL 2013 still goes under the window name XLMAIN as is the case in versions '97 - 2010?

Many thanks,
Pieter

Comment by: Jan Karel Pieterse (23-1-2014 10:08:34) deeplink to this comment

Hi Pieter,

As far as I know: yes.

Comment by: Ton van Munsteren (30-1-2014 09:27:05) deeplink to this comment

Hi Jan Karel

Just got Excel 2013. I have built complex applications in VBA with several forms and several workbooks. With MDI in Excel 2010 no problem, but nothing works anymore in Excel 2013.
Found the example code on your site very useful, but still have some questions:
Should I copy the entire code into each form (and of course adapt it to my needs)?
Even if five forms deal with data in one workbook and three with another workbook?

Thanks,
Ton

Comment by: Jan Karel Pieterse (30-1-2014 13:24:04) deeplink to this comment

Hi Ton,

Depends. You should typically only use the technique on a form which may cause (or need) a switch in the active window.

Comment by: Dennis (5-6-2014 21:10:13) deeplink to this comment

Hi,
I have spent a lot of time to solve this for my needs.
I really don't like using Application.OnTime and therefore the class solution mentioned here is not for me. Thanks for sharing it though.

I liked the solution mentioned in the beginning, found at:
http://cpap.com.br/cd.asp?F=ModelessformOnTop002.zip
but it works better if you change this:

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

to this:

Private Sub HostApp_WindowDeactivate(ByVal Wb As Workbook, ByVal Wn As Excel.Window)
    SetWindowLongA mhwndForm, GWL_HWNDPARENT, 0&
End Sub

Else, if you close a workbook/window and press cancel, you will loose your userform.

Also, to solve the problem that "the userform "steals" back the focus when you click in Excel and then switch to a different workbook." I added the code below IN CAPITALS:

Private Sub HostApp_WindowActivate(ByVal Wb As Workbook, ByVal Wn As Window)
    If Val(Application.Version) >= 15 And mhwndForm <> 0 Then
        DIM CURRENTWINDOWHANDLE AS LONG
        CURRENTWINDOWHANDLE = GETFOREGROUNDWINDOW
            mXLHwnd = Application.HWnd            SetWindowLongA mhwndForm, GWL_HWNDPARENT, mXLHwnd
            SetForegroundWindow mhwndForm
        SETFOREGROUNDWINDOW CURRENTWINDOWHANDLE
    End If
End Sub

Needs this in the declaration too:
Declare PtrSafe Function GetForegroundWindow Lib "user32" () As Long

Hope this helps somebody, coming to this page searching for a solution, the way I did.

All the best / Dennis

Comment by: Jan Karel Pieterse (6-6-2014 11:51:08) deeplink to this comment

Hi Dennis,

Thanks for the suggestions, very helpful.
You can tell I am still not using Excel 2013 as mu production application, otherwise I would probably have bumped into these issues with my own add-ins and acted accordingly :-).

Comment by: Dennis (6-6-2014 12:39:33) deeplink to this comment

Thanks,

Actually I just had to make another small change.
If you hide the first workbook/window that creates the userform with
Windows(ThisWorkbook.Name).Visible = False
because you only want the userform, then open a few workbooks/windows and close them again the userform will not be shown when you come back to the first workbook/window as the function
HostApp_WindowActivate
will not be triggered when the workbook/window is hidden. :(

Here is my solution that seems to fix it:

1. In the api declaration add
Declare PtrSafe Function GetWindowLongA Lib "user32" (ByVal hwnd As Long, ByVal nIndex As Long) As Long

2. In the conditional declaration add
Dim FirstWindow As LongPtr

3. In UserForm_Initialize add
FirstWindow = GetWindowLongA(mhwndForm, GWL_HWNDPARENT)

4. In HostApp_WindowDeactivate
change
SetWindowLongA mhwndForm, GWL_HWNDPARENT, 0&
to
SetWindowLongA mhwndForm, GWL_HWNDPARENT, FirstWindow

KR / Dennis

Comment by: Didier (29-9-2014 16:56:39) deeplink to this comment

Hi,

Congratulations for the code !

I tried to adapt to PowerPoint but I can't succeed.
Could you help me please ?
What must be change in the first code ?

Thanks if you can help.

Best,

Didier

Comment by: Jan Karel Pieterse (29-9-2014 17:10:36) deeplink to this comment

Hi Didier,

Untested (part 1):

Option Explicit

'Object variable to trigger application events
Private WithEvents PPTApp As PowerPoint.Application

#If VBA7 Then
    Dim mPPTHwnd 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 mPPTHwnd 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

Comment by: Jan Karel Pieterse (29-9-2014 17:10:49) deeplink to this comment

Hi Didier,

Untested (part 2):

Private Sub PPTApp_PresentationBeforeClose(ByVal Pres As Presentation, Cancel As Boolean)
    If Not Me.Visible Then Me.Show vbModeless
End Sub

Private Sub PPTApp_WindowActivate(ByVal Pres As Presentation, ByVal Wn As DocumentWindow)
    If Val(Application.Version) >= 15 And mhwndForm <> 0 Then 'Basear o form na janela ativa do Excel.
        mPPTHwnd = Application.hwnd    'Always get because in PPT 15 SDI each presentation has its window with different handle.
        SetWindowLongA mhwndForm, GWL_HWNDPARENT, mPPTHwnd
        SetForegroundWindow mhwndForm
    End If
End Sub

Private Sub UserForm_Initialize()
    If Val(Application.Version) >= 15 Then        'Only makes sense on Excel 2013 and up
        Set PPTApp = Application
        mhwndForm = FindWindowA("ThunderDFrame", Caption)
    End If
End Sub

Comment by: didjee.did (7-10-2014 15:09:32) deeplink to this comment

Hi everybody,
Thaznks to Jan Karel for help to adapt the macro to PowerPoint.
Unfortunately it does not work really for PPT.
the userform switch well from the first presentation to the second but as soon as we click again on these second presentation, the userform is sent backwards as if it was always linked to the first presentation :(.
Is there someone who could help me please ?
Thanks
Didjee

Comment by: Didjee (20-10-2014 12:45:10) deeplink to this comment

Hi

Is there anybody for help to apply the solution of Jan Karel that does not work with PowerPoint ? ? ? ? ?

Help please!

Didier

Comment by: Didier (27-11-2014 20:36:49) deeplink to this comment

Hi Jan Karel,
Could you please forward to Dennis that although the changes are made, when you close any worksheet the userform disappears.
I did not find a solution for this issue...
Maybe Dennis will get one.
Thanks for all.
Didier

Comment by: Greg Schlitt (13-4-2015 23:27:06) deeplink to this comment

Thanks very much for this solution, which is working in my VBA application. My question is (from an API neophyte) if I have several windows open when the form is triggered, which window "owns " the form? At the moment my application opens three windows, then the form is triggered, all at startup. Shutting down two of the windows kills off the form, but not the third (!?)

Ideally I'd like to have the form owned by some minimized window so that if the user shut downs any one of the three standard windows the form is *not* killed off. Even better if would be owned by the application itself, not by any window opened by my vba code.

Apologies if that's nonsensical! I'm new to all this.

Comment by: Jan Karel Pieterse (13-4-2015 23:41:42) deeplink to this comment

Hi Greg,

Basically, at the outset the form is owned by the Excel window from which it was launched. Which can be a challenge to detect sometimes, depending on what triggers the form to show exactly.

Comment by: Didjee (12-11-2015 20:02:47) deeplink to this comment

Hi,
One year later nobody finds a solution to the fact that a userform created from a .ppam add-in can't no more stay on top and is linked to the first file open in PowerPoint ! It will not work with any other file and will disappear backwards...
This is a huge Microsoft bug that will never be fixed.

Comment by: Ben Norris (19-9-2016 13:27:05) deeplink to this comment

Hi Jan,

A bit late to the game on this one but was just looking at the code and wondered about the #If Win64 compiler directive... Should this be in the #VBA7 branch of code? Surely you can't have 64-bit Office on 32-bit OS? Should that directive be moved to the other branch and the 'PtrSafe' removed (i.e. only check if on a 64-bit OS if Office is 32-bit)?

Interested to hear your thoughts.

Ben.

Comment by: Jan Karel Pieterse (19-9-2016 14:40:33) deeplink to this comment

Hi Ben,

TBH I'm not that well into API functions. I think the Win64 is needed because in case we're using 64 bit Office we need to call a 64 bit function in the user32 dll, which is aliased as "SetWindowLongPtrA". But I'm out of my depth here.

Comment by: Bill (2-1-2017 23:06:40) deeplink to this comment

I'm getting an error

---------------------------
Microsoft Visual Basic for Applications
---------------------------
Compile error:

User-defined type not defined
---------------------------
OK Help
---------------------------

The "Dim mcTopMost As clsTopMost" is highlighted.

What am I missing?

Comment by: Jan Karel Pieterse (3-1-2017 11:28:08) deeplink to this comment

Hi Bill,

Looks like you have not copied the class module clsTopMost into your project.

Comment by: Bill (3-1-2017 12:41:45) deeplink to this comment

Thank you for responding.

I copied everything in that section except for the comments. I put it into a Class module. However, I do not see clsTopMost mentioned in other than the comments.

Comment by: Jan Karel Pieterse (3-1-2017 20:18:41) deeplink to this comment

Hi Bill,

You must cnage the NAME of that class module to clsTopMost.

Comment by: Bill (3-1-2017 21:11:39) deeplink to this comment

Thank you. I changed the name of the class form module also. The code ran but that didn't solve my problem. I have a spreadsheet with very many rows and few columns so I made a "4 up" display by having 4 windows across the screen each with a subsequent portion of the rows. There is also a modeless form displayed. That all works fine until a selection of a cell. Then the modeless form disappears and the focus turns to the first window regardless of which window the cell's original location.

Comment by: Jan Karel Pieterse (4-1-2017 08:19:35) deeplink to this comment

Hi Bill,

Does the demo file accompanying this article work as expected?

Comment by: Bill (4-1-2017 13:16:10) deeplink to this comment

No it not only did not it made a mess of the default setting app so that xls file no longer point to Excel. And I can't seem to change the settings.

Comment by: Jan Karel Pieterse (4-1-2017 13:21:03) deeplink to this comment

Hi Bill,

Odd, there is no code there that would cause that to happen?

Comment by: Bill (4-1-2017 13:33:50) deeplink to this comment

No I'm sure there wasn't but that doesn't help the situation.

Comment by: Jan Karel Pieterse (4-1-2017 14:59:23) deeplink to this comment

Hi Bill,

If restarting the PC does not resolve this I suggest to do a repair of Office to fix the file association problem.

Comment by: Bill (4-1-2017 15:07:15) deeplink to this comment

It turns out that in Windows 10 and Excel 2016 on may not open an .xls file.

Comment by: Jan Karel Pieterse (4-1-2017 15:43:11) deeplink to this comment

Hi Bill,

This is a security setting in Excel I believe, File, Options, trust center, Trust center settings, File Block settings.

Comment by: Bill (4-1-2017 20:50:06) deeplink to this comment

Well thank you for that. I was able to open the file but there doesn't appear to be as much code as above.

Comment by: Jan Karel Pieterse (5-1-2017 06:20:55) deeplink to this comment

Hi Bill,

Apologies for confusing you. This article in fact contains two methods. The most efficient one is listed first (code ends just above chapter "Conclusion"), the more convoluted solution is listed next (the one with the class). I assumed you were trying to implement the second one whereas the first is the recommended method. It is the first one which is used in the demo file.

Comment by: john.davidson@btinternet.com (19-2-2017 02:48:14) deeplink to this comment

I have a similar problem in MS-Word (2003). I have a modeless userform that I want to keep on top of all other Word windows/documents. I've found a way on the www to keep it on top of ALL windows, including other apps., but this can be too radical (like when the userform calls a modal MsgBox, and then sits on top of it). Can the above code be adapted for Word?

Thanks

John D.

Comment by: Jan Karel Pieterse (20-2-2017 10:06:06) deeplink to this comment

This is the code in Word:

Option Explicit

'Object variable to trigger application events
Private WithEvents WDApp As Word.Application

#If VBA7 Then
    Dim mWDHwnd As LongPtr    'Word'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 mWDHwnd As Long    'Word'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()
    Set WDApp = Application
    mhwndForm = FindWindowA("ThunderDFrame", Caption)
End Sub

Continued...

Comment by: Jan Karel Pieterse (20-2-2017 10:06:28) deeplink to this comment

Continued...

Private Sub WDApp_DocumentBeforeClose(ByVal Doc As Document, Cancel As Boolean)
    SetWindowLongA mhwndForm, GWL_HWNDPARENT, 0&
End Sub

Private Sub WDApp_WindowActivate(ByVal Doc As Document, ByVal Wn As Window)
'    If Val(Application.Version) >= 15 And mhwndForm <> 0 Then 'Basear o form na janela ativa do Word.
        mWDHwnd = FindWindowA("OpusApp", Caption)    'Always get because in Word 15 SDI each wb has its window with different handle.
        SetWindowLongA mhwndForm, GWL_HWNDPARENT, mWDHwnd
        SetForegroundWindow mhwndForm
'    End If
End Sub

Private Sub WDApp_WindowSize(ByVal Doc As Document, ByVal Wn As Window)
    If Not Me.Visible Then Me.Show vbModeless
End Sub

Comment by: Wojtek (7-6-2017 02:03:31) deeplink to this comment

And how do you deal with closing UserForm when you close the active workbook when it was lunched?
E.g. I create user form with command buttons, progress bar and so on after I lunch workbook with source data. But then I want to close the original file and show processed data in the new workbook(s). But after I close source data workbook, also user form is closed (because it's child object of this workbook I assume).

Comment by: Jan Karel Pieterse (7-6-2017 06:53:39) deeplink to this comment

Hi Wojtek,

Excellent point, two problems arise:

1. The Save changes dialog pops up behind the userform and the userform cannot be moved, leaving the user in a "Excel is not responding"-like state

2. The form is hidden when the file is closed, but VBA does not think so and fails to show it again

Comment by: Wojtek (7-6-2017 14:46:37) deeplink to this comment

Hello Jan,

I currently struggle with the second issue. But for sure the first will also come out.
Do you suggest to open separate workbook just for the userform before it's lunched and then e.g. hide the workbook?

Comment by: Jan Karel Pieterse (7-6-2017 14:49:31) deeplink to this comment

Hi Wojtek,

I haven't had time to look at these issues yet, they do need addressing though. One thing you might investigate is if you can simply use an applicaton event class which has a workbook_BeforeClose event handler. In there you could add clean-up code such as closing your forms.

Comment by: michael lalonde (24-6-2017 00:11:02) deeplink to this comment

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 (26-6-2017 14:49:08) deeplink to this comment

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 (3-7-2017 20:48:37) deeplink to this comment

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 (3-7-2017 22:21:30) deeplink to this comment

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 (26-9-2017 23:03:26) deeplink to this comment

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 (27-9-2017 10:29:22) deeplink to this comment

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 (27-9-2017 16:14:38) deeplink to this comment

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 (27-9-2017 16:24:47) deeplink to this comment

Hi Jessie,

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

Comment by: Richard Haaf (21-11-2017 12:30:53) deeplink to this comment

Wow. I've been struggling with this for more than 2 years. Ever since they pushed Excel 2013 to all our workstations, the dialog-boxes in Excel have been appearing behind the worksheets. After adding your code, I now have a dropdown on the dialog box where I can select the parent worksheet/workbook. The selected sheet moves to the front, and the Dialog-Box appears above it. No more hidden dialog-boxes. Thanks a million.

Comment by: Walter Knipp (19-1-2018 20:05:45) deeplink to this comment

hallo,
i use excel-makros since 15 years, include userforms.
now i changed from office2010 to office2016 on my win10 64bit single pc and i have trouble with excel.
when i want to open an excel-workbook with userforms in it an errormessage occurs, that this is not possible.
then i open a new workbook and build a makro, thats fine. but i cannot insert an UserForm.
the error 336 , active-x komponent not right registered.
i deinstall the office with the microsoft clean-deinstallation and install again.
on a second win10 64bit Office2016 pc everything is ok, i see no different.
i tried regsvr32 FM20.DLL and others, no chance.
any ideas?
sorry for my bad englisch , i'm a german.
walter

Comment by: Jan Karel Pieterse (22-1-2018 10:09:15) deeplink to this comment

Guten Tag Walter,

Your English is just fine :-)

Did you remove the Office 2010 installation prior to installing 64 bit 2016? If not I suggest to do so as a mixed 32 and 64 bit setup of two versions of Office doesn't work very well.

Comment by: Walter Knipp (22-1-2018 11:29:39) deeplink to this comment

hallo again,
thank you for help, but i deinstall the office2010 bevor install the new one. may be i have to delete more than i now. but here a neu issue, the vba under word2016 works fine. here i can create the userforms. mysteriously....
walter

Comment by: Jan Karel Pieterse (22-1-2018 14:15:11) deeplink to this comment

Hi Walter,

I would try to remove the user from the system (after making a backup of all user data!) and adding the user back again.
ANother thing to try is to empty all temporary folders of Windows (enter %temp% in the Windows Explorer address bar and press enter)

Comment by: Jan Karel Pieterse (22-1-2018 16:38:11) deeplink to this comment

Hi Walter,

Suggestion by Peter Thornton:

"First thing to try is delete ‘Excel.box’ under user-name/AppData/Roaming/Microsoft/Forms. It stores tool-box data, if it exists after upgrading might be responsible for the problem"

Comment by: Roger Mepham (4-4-2018 16:02:06) deeplink to this comment

Making a PowerPoint slideshow window stay on top of all other windows.

Hi - I'm trying to get a button on a PowerPoint slideshow window to make the window float above all other windows.

This code works in excel

'Update 20140909
#If Win64 Then
    Public Declare PtrSafe Function 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) _
    As Long
#Else
    Public Declare Function 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) _
    As Long
#End If
Public Const SWP_NOSIZE = &H1
Public Const SWP_NOMOVE = &H2
Public Const HWND_TOPMOST = -1
Public Const HWND_NOTOPMOST = -2
Sub ShowXLOnTop(ByVal OnTop As Boolean)
    Dim xStype As Long
    #If Win64 Then
        Dim xHwnd As LongPtr
    #Else
        Dim xHwnd As Long
    #End If
    If OnTop Then
        xStype = HWND_TOPMOST
    Else
        xStype = HWND_NOTOPMOST
    End If
    Call SetWindowPos(Application.hwnd, xStype, 0, 0, 0, 0, SWP_NOSIZE Or SWP_NOMOVE)
End Sub
Sub SetXLOnTop()
    ShowXLOnTop True
End Sub
Sub SetXLNormal()
    ShowXLOnTop False
End Sub

But using in PowerPoint I get an error which I'm guessing is because the Slideshow window handle is wrong.

Any help you could provide would be gratefully received.

Thanks
Roger Mepham

Comment by: Jan Karel Pieterse (5-4-2018 07:54:18) deeplink to this comment

Hi Roger,

I assume you are correct that you are using the wrong hwnd. It seems the classname for the presentation window is "screenClass" so perhaps you can use the FindWindowA API function to get its hwnd from the classname?
I am wondering why you would want to do this however, PPT is already very "always on top" in my experience :-)

Comment by: William Howard Benson (3-7-2018 09:10:52) deeplink to this comment

Hi Jan Karel,

I work for KPMG, they are finicky about using any code posted on the web due to possibility of copyright infringement. Unlike Stack Exchange, which I think is pretty much open source, I want to respect your copyright terms ... can you tell me whether these code solutions you post here are free of any licensing concerns for me or my company? Happy to post your web address and name in the modules where used.

Thanks!

Comment by: Jan Karel Pieterse (3-7-2018 10:23:48) deeplink to this comment

Hi Bill,

You are welcome to use the code, thank you for asking.
I would like to ask for:
- Attribution including a link to the page in question
- Aks for permission when used in a commercial application
This reminds me I should write something about this on my site.

Comment by: Jesse Banner (8-8-2018 21:11:30) deeplink to this comment

Hello, I am trying to use the older code to keep the form on top across files, however anytime the form is selected or used the active workbook changes back to the file the form was called from. Is there a way around that?

Thanks!

Comment by: Jan Karel Pieterse (8-8-2018 21:12:41) deeplink to this comment

Hi Jesse,

Why are you trying to use the old code?

Comment by: Jesse Banner (9-8-2018 07:30:02) deeplink to this comment

The reason is the new code reloads the forms whenever another workbook is activated and my forms lose the data in them, particularly the combo box selections, etc. I can prevent it with EnableEvents = False before activation but it means going through thousands of calls for activation to be sure enable events get set and set back properly or just calling other workbooks without activating them which is also a lot of coding.

Comment by: Jan Karel Pieterse (9-8-2018 11:49:01) deeplink to this comment

Hi Jesse,

Odd, because .Hide followed by a .SHow in itself should not cause your controls to reset. It is likely that you are using tha userform activate event to initialize its controls, rather than using the Initialize event of the form.

Comment by: Jim Dawson (18-4-2019 16:41:00) deeplink to this comment

Hi,

This is most excellent. I searched high and low for a solution to the "SDI UserForm" snafu. Nothing worked. I tried yours with a workbook having two user forms by placing the code in both user form modules (required) and it worked fine also.

Perhaps you could show how to use it in a class by itself, once, to avoid unnecessary duplication. I did not feel up to hacking to try it myself.

Comment by: Jan Karel Pieterse (18-4-2019 17:21:00) deeplink to this comment

Hi Jim,

Excellent suggestion. I have updated the page and the download accordingly.

Comment by: Charlie Procaccini (24-5-2019 14:12:00) deeplink to this comment

Hi Jan,

Good solution a nagging problem. The issue I have is that if you minimize a workbook an error is generated in the resize event.

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

Comment by: Jan Karel Pieterse (24-5-2019 16:47:00) deeplink to this comment

Hi Charlie,

Which error do you get?

Comment by: Philippus J Opperman (29-6-2019 21:35:00) deeplink to this comment

Good day Jan Karel
As per all the comments this works excellent, but I also get an error in "Sub XLApp_WindowResize" indicating Compile error: Method or data member not found. The problem seems to be with me.visible is ".visible" is highlighted.

This is happening when I am moving one of the workbooks while the form that needs to stay on top is open.

Comment by: Jan Karel Pieterse (1-7-2019 10:41:00) deeplink to this comment

Hi Philippus,

Thanks for reporting, indeed the code was wrong.

I have updated the page and the download which now works as it should.

The problem was with the declaration type of both moTheUserform and of the argument type declaration in the TheUserform Property Set method. Both should have been "Object", rather than "Userform".

Comment by: Philippus J Opperman (1-7-2019 21:11:00) deeplink to this comment

Thanks for making the fix available. Highly appreciated. It works perfectly now. SDI with one of my forms was a big hurdle in one of my macro and your solution solved all of it. Thanks a million!

Comment by: Rudy (15-8-2019 22:37:00) deeplink to this comment

Hello, I am hoping someone here much smarter than myself can help me as I have pretty much lost all my hair over this issue. I have a master workbook that has all forms and code in it. No interaction is allowed with the workbook, worksheets or toolbar. The forms open other workbooks that need to be updated by inputs into the forms (same with these workbooks no interaction allowed outside of the forms). Printing, scrolling and creation of new tabs are all handled by the forms as well. The SDI in 2016 of course breaks this completely and I cannot find a way to make it work. I was hoping this solution would work but it has a behavior I was not expecting... the master workbook stays visible along with the form. With 2010 the new workbook was displayed with the form over it. If you set focus to the new workbook the form is no longer displayed until you set focus to the master workbook. Can anyone please point me in the right direction? Thank you all

Comment by: Jan Karel Pieterse (27-8-2019 10:48:00) deeplink to this comment

Hi Rudy,

First of all, if your main workbook does not need any interaction of the user and never needs to be saved consider saving it as an add-in.

For the rest, you should be able to use the code on this page to achieve what you need: a userform that stays on top of any Excel window.

Comment by: Dan (11-4-2020 07:28:00) deeplink to this comment

DUDE!!! YOU ARE THE MAN!!! STILL WORKS in 2020. WOW!!!

Can we all just love you for a moment. What a timesaver and mental anxiety eliminator.

Comment by: Davide (22-4-2021 01:59:00) deeplink to this comment

Hi. Thank you for your excellent solution and explanation.
I understand that it works when launched from Excel.
I am looking for a similar solution that works when the userform is launched from PowerPoint (and it then has to stay on top of an Excel window).Do you have any idea of how this might work?

Thank you.

Comment by: Jan Karel Pieterse (22-4-2021 12:01:00) deeplink to this comment

'Part 2 of code
Private Sub UserForm_Click()
    #If VBA7 Then
        Dim lngParm As LongPtr
    #Else
        Dim lngParm As Long
    #End If
    mhwndForm = FindWindow32("ThunderDFrame", Me.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)
    With CreateObject("Excel.Application")
        .Visible = True
        .workbooks.Add
    End With
End Sub

Comment by: Jan Karel Pieterse (22-4-2021 12:01:00) deeplink to this comment

Hi Davide,

A simple way would be to make the form stay on top of all windows. This code behind a userform responds to a click on the form by opening a new Excel window.

Part 1 of code, remainder in next message:

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

Comment by: Rashid Mahmood (11-6-2021 14:20:00) deeplink to this comment

Dear kindly advise how to use these codes for multiple user forms being loaded one after the other with a group of workbooks? Should we have to write the code with all user forms individually while intializing?

Comment by: Jan Karel Pieterse (11-6-2021 15:43:00) deeplink to this comment

Hi Rashid,

Yes, this needs to be implemented in each userform.

Comment by: Alex (3-9-2021 13:20:00) deeplink to this comment

Hello,
Thank you very for addressing this annoying issue.
I could not dive yet but I simply followed your instructions here and could not make my form to stay on top when I activate another workbook.

My form is part of a plugin which opens 2 other workbooks. after the workbooks are displayed, my form appears actually on top, but as soon as I click in any of the workbooks, they switch over my userform :/

My Initialize procedure starts like that:
Private Sub UserForm_Initialize()
Dim res As Boolean

On Error GoTo UserForm_Initialize_error

'>>>>>> Test of class cFormOntop <<<<<<
Set mclsFormOnTop = New cFormOnTop
Set mclsFormOnTop.TheUserform = Me
'<<<<<<

Me.Caption = "syncScroll " & SC_VERSION
.../...

Are there some restrictions in the class you gave or some limitations I might break in my code ?
Thank you for any hint...

Comment by: Jan Karel Pieterse (13-9-2021 10:09:00) deeplink to this comment

Hi Alex,

Have you declared mclsFormOnTop at the top of the userforms module?

Comment by: Justin Oakley (26-5-2022 14:21:00) deeplink to this comment

Thanks for providing this functionality. I had given up looking for something that allows a form to float on top of whatever workbook is active. It opens the door to me being able to develop the project that I have been thinking about for a while.

Comment by: Jan Karel Pieterse (27-5-2022 14:05:00) deeplink to this comment

Hi Justin,

You're welcome!

Comment by: RKane (17-11-2022 17:11:00) deeplink to this comment

Nice work! Thank you!

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.