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

Audit !!!

Check out our RefTreeAnalyser
the ultimate Excel formula auditing tool.

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

All comments about this page:


Comment by: Chip Pearson (2/1/2013 9:59:24 PM)

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 3:03:22 PM)

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 (2/18/2013 10:58:39 AM)

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

 


Comment by: Jan Karel Pieterse (2/18/2013 11:55:38 AM)

Hi JTsang,

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

 


Comment by: JTsang (2/18/2013 2:41:41 PM)

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 (2/18/2013 4:58:23 PM)

Hi JTsang,

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

 


Comment by: Dutch Gemini (7/4/2013 12:17:15 PM)

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 (7/4/2013 5:27:39 PM)

Hi Dutch Gemini,

Thanks!

 


Comment by: Dutch Gemini (7/4/2013 5:53:09 PM)

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 (9/6/2013 6:11:39 AM)

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 (9/7/2013 4:25:55 PM)

Hi Debbie,

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

 


Comment by: Debbie Grebenc (9/9/2013 6:31:34 PM)

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 6:35:31 PM)

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 (12/17/2013 8:55:45 PM)

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 (12/17/2013 9:13:49 PM)

Hi Roman,

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

 


Comment by: Pieter (1/22/2014 5:08:47 PM)

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 (1/23/2014 10:08:34 AM)

Hi Pieter,

As far as I know: yes.

 


Comment by: Ton van Munsteren (1/30/2014 9:27:05 AM)

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 (1/30/2014 1:24:04 PM)

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 (6/5/2014 9:10:13 PM)

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 AM)

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 PM)

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 (9/29/2014 4:56:39 PM)

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 (9/29/2014 5:10:36 PM)

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 (9/29/2014 5:10:49 PM)

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 (10/7/2014 3:09:32 PM)

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 (10/20/2014 12:45:10 PM)

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 (11/27/2014 8:36:49 PM)

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 (4/13/2015 11:27:06 PM)

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 (4/13/2015 11:41:42 PM)

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 (11/12/2015 8:02:47 PM)

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 (9/19/2016 1:27:05 PM)

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 (9/19/2016 2:40:33 PM)

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 (1/2/2017 11:06:40 PM)

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 (1/3/2017 11:28:08 AM)

Hi Bill,

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

 


Comment by: Bill (1/3/2017 12:41:45 PM)

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 (1/3/2017 8:18:41 PM)

Hi Bill,

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

 


Comment by: Bill (1/3/2017 9:11:39 PM)

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 (1/4/2017 8:19:35 AM)

Hi Bill,

Does the demo file accompanying this article work as expected?

 


Comment by: Bill (1/4/2017 1:16:10 PM)

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 (1/4/2017 1:21:03 PM)

Hi Bill,

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

 


Comment by: Bill (1/4/2017 1:33:50 PM)

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

 


Comment by: Jan Karel Pieterse (1/4/2017 2:59:23 PM)

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 (1/4/2017 3:07:15 PM)

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

 


Comment by: Jan Karel Pieterse (1/4/2017 3:43:11 PM)

Hi Bill,

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

 


Comment by: Bill (1/4/2017 8:50:06 PM)

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 (1/5/2017 6:20:55 AM)

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 (2/19/2017 2:48:14 AM)

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 (2/20/2017 10:06:06 AM)

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 (2/20/2017 10:06:28 AM)

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 (6/7/2017 2:03:31 AM)

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 (6/7/2017 6:53:39 AM)

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 (6/7/2017 2:46:37 PM)

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 (6/7/2017 2:49:31 PM)

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 (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 :-)

 


Comment by: Richard Haaf (11/21/2017 12:30:53 PM)

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.

 


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].