Userform always on top

I found this a while back and saved it... can't take credit for it.

' Written:  July 07, 2009
' Author:   Leith Ross
' Summary:  Keeps a UserForm, or any window on top of all other windows.
'           Call this macro from the UserForm_Activate() event code module.


'Returns the Window Handle of the Window that is accepting User input.
Private Declare Function GetForegroundWindow Lib "user32.dll" () As Long

Private Declare Function SetWindowPos _
  Lib "user32.dll" _
    (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
Sub KeepFormOnTop()
  
    Const HWND_TOPMOST  As Long = -1
    Const SWP_NOMOVE    As Long = &H2
    Const SWP_NOSIZE    As Long = &H1
    
        SetWindowPos GetForegroundWindow(), HWND_TOPMOST, 0, 0, 0, 0, SWP_NOMOVE + SWP_NOSIZE

End Sub

Now I look carefully you are using a version of this already, so it may not help...

Guess you've upgraded to 2013 or 2016 rather than the 2010 mentioned in your profile...?

EDIT: The post above (and your original code) has the disadvantage that the userform will be shown over other messages, like error messages... and, if big enough, will completely hide them.

Ok here's another try... (testing on my end works with this method)

At the top of your userform code:

Private Declare Function FindWindow Lib "user32.dll" Alias "FindWindowA" ( _
                    ByVal lpClassName As String, _
                    ByVal lpWindowName As String) As Long
                    
Private 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

Private Const HWND_TOPMOST = -1
Private Const HWND_NOTOPMOST = -2
Private Const SWP_NOMOVE = &H2
Private Const SWP_NOSIZE = &H1
Private mlHwnd As Long

And add this to the userform_initialize code (or put all the code together in a module, put the below code in a sub, and call it from the userform initialize):

mlHwnd = FindWindow("ThunderDFrame", "UserForm1") 'Change "UserForm1" to match the CAPTION (not name) of your userform
    Do While mlHwnd = 0
        mlHwnd = FindWindow("ThunderDFrame", "UserForm1") 'Change "UserForm1" to match the CAPTION (not name) of your userform
        DoEvents
    Loop
    'Set topmost
    SetWindowPos mlHwnd, HWND_TOPMOST, 0, 0, 0, 0, SWP_NOMOVE Or SWP_NOSIZE

See this recent thread...

@Arkadi: If your profile is correct (XL2010), it will work - see post #2 in the link for an explanation.

@ cytop, thanks, didn't realize this won't work in later versions. My first code post (#2) would not keep the userform on top of another instance of excel, while my last post (#6) DID stay above all other applications... I was hoping maybe that latest post would work in later versions too... but perhaps not.

Just tested in a 2013 version of office, and my post #6 does seem to keep the userform above any other application windows without problem.

does seem to keep the userform above any other application

Yes, it will as you're setting it TOPMOST (above all other Windows) - but that's introduces other issues especially the fact an error message or a notification from another application, can be hidden - as the OP mentioned, it's probably not a problem here, but I would pick that up in any code review and send it back for re-work. That's just me, though.

Apols in a way - I didn't mean to imply it would not work in other versions - my error by omission, I didn't make myself clear.

No worries at all cytop , I appreciate the feedback, and see no need at all for you to apologize! .... I do understand there are drawbacks, perhaps I too ought to have put a warning along with the code. I took the OP's requirement to be what that code does, but it is important to understand the consequences