Userform that always stays on top but allows interacting with forms behind it

[chazrab]

The following code works to a point but won't allow interacting with forms behind it:
In standard Module 13:

' Place this code in a standard module
Declare PtrSafe Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal lpClassName As LongPtr, ByVal lpWindowName As LongPtr) As LongPtr
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
Public Const HWND_TOPMOST As LongPtr = -1
Public Const SWP_NOMOVE As Long = &H2
Public Const SWP_NOSIZE As Long = &H1
Public Const SWP_NOACTIVATE As Long = &H10
Public Const SWP_NOZORDER As Long = &H4
Public Const SWP_SHOWWINDOW As Long = &H40
Public Const HWND_NOTOPMOST As LongPtr = -2

Userform ONTOPFORM code:

Private Sub UserForm_Activate()
    Application.OnTime Now + TimeValue("00:00:01"), "KeepFormOnTop"
End Sub

Private Sub UserForm_Deactivate()
    On Error Resume Next
    Application.OnTime Now + TimeValue("00:00:01"), "KeepFormOnTop", , False
End Sub

I cannot figure out why this code is keeping the small ONTOPFORM "locked" not allowing interaction with the form or forms behind it.
I've seen this done but cannot find the correct code.
64 bit machine, Windows API calls.

Any help much appreciated
cr

 

[Logit]

Did you set the Modal property to 'False' ?

 

[chazrab]

Did you set the Modal property to 'False' ?

Doesn't work. The form that supposed to stay on top disappears when the focus moves to a form behind it but then reappears when that form loses focus - keeping an Excel userform always on top and being able to interact with other userforms and/or windows behind it involves
a bit more than property changes - it involves making changes to the Window API calls in a standard module and in the userform itself
I once had the correct code to do this but it got lost in the many changes I've made to this application.

 

[Logit]

Paste the following code into the UserForm you wish to always have on top.

Option Explicit

'API function to enable/disable the Excel Window
Private Declare Function FindWindowA Lib "user32" _
(ByVal lpClassName As String, ByVal lpWindowName As String) As Long
Private Declare Function EnableWindow Lib "user32" _
(ByVal hwnd As Long, ByVal bEnable As Long) 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 SWP_NOSIZE = &H1
Private Const SWP_NOMOVE = &H2
Private Const FLAGS As Long = SWP_NOMOVE Or SWP_NOSIZE
Private Const HWND_TOPMOST = -1
Private Const HWND_NOTOPMOST = -2
Private mlHWnd As Long
Private mbDragDrop As Boolean
Private FormHWnd As Long
 
Private Sub cmdNotTop_Click()
    SetWindowPos FormHWnd, HWND_NOTOPMOST, 0, 0, 0, 0, FLAGS
End Sub
 
Private Sub cmdTop_Click()
    SetWindowPos FormHWnd, HWND_TOPMOST, 0, 0, 0, 0, FLAGS
End Sub
 
Private Sub UserForm_Activate()
    On Error Resume Next
    'Find the Excel main window
    mlHWnd = FindWindowA("XLMAIN", Application.Caption)
    FormHWnd = FindWindowA(vbNullString, Me.Caption)
    Call cmdTop_Click
    'Enable the Window - makes the userform modeless
    EnableWindow mlHWnd, 1
    mbDragDrop = Application.CellDragAndDrop
    Application.CellDragAndDrop = False
End Sub
 
Private Sub btnOK_Click()
    Application.CellDragAndDrop = mbDragDrop
    Call cmdNotTop_Click
    Unload Me
End Sub

All forms Modal Property need to be set to FALSE.

Download sample workbook : Internxt Drive – Private & Secure Cloud Storage

 

[chazrab]

Pasted at top of form. Function word highlighted in blue.

 

[Logit]

Option Explicit

'API function to enable/disable the Excel Window
Private Declare Function FindWindowA Lib "user32" ( _
    ByVal lpClassName As String, ByVal lpWindowName As String) As Long
Private Declare Function EnableWindow Lib "user32" ( _
    ByVal hwnd As Long, ByVal bEnable As Long) 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 SWP_NOSIZE = &H1
Private Const SWP_NOMOVE = &H2
Private Const FLAGS As Long = SWP_NOMOVE Or SWP_NOSIZE
Private Const HWND_TOPMOST = -1
Private Const HWND_NOTOPMOST = -2

Private mlHWnd As Long
Private mbDragDrop As Boolean
Private FormHWnd As Long

Private Sub cmdNotTop_Click()
    SetWindowPos FormHWnd, HWND_NOTOPMOST, 0, 0, 0, 0, FLAGS
End Sub

Private Sub cmdTop_Click()
    SetWindowPos FormHWnd, HWND_TOPMOST, 0, 0, 0, 0, FLAGS
End Sub

Private Sub UserForm_Activate()
    On Error Resume Next
    'Find the Excel main window
    mlHWnd = FindWindowA("XLMAIN", Application.Caption)
    FormHWnd = FindWindowA(vbNullString, Me.Caption)
    Call cmdTop_Click
    'Enable the Window - makes the userform modeless
    EnableWindow mlHWnd, 1
    mbDragDrop = Application.CellDragAndDrop
    Application.CellDragAndDrop = False
End Sub

Private Sub btnOK_Click()
    Application.CellDragAndDrop = mbDragDrop
    Call cmdNotTop_Click
    Unload Me
End Sub