Modeless Userform Minimizes and Maximizes with Workbook

[Mrock1]

I have and Excel-based application that only occasionally needs user access to the underlying workbook. A macro, triggered by an OnTime event (triggers every 15 minutes), shows a 1st userform using the command Userform1.show vbModeless. Within that user form, I use the userform_activate event to set it to be always on top (SetWindowPost(Hwnd1, -1, 0, 0, 0, 0, 3)), which works exactly as intended. Subsequently I can minimize and restore the underlying workbook to my heart's content without affecting the visible 1st userform. However, on that 1st userform, I have a command button that loads a 2nd userform - also modeless (Userform2.show vbModeless). This too has a userform_activate that uses the SetWindowPos(Hwnd2, -1, 0, 0, 0, 0, 3). Initially, this seems to work correctly and both userforms are visible and always on top, with the 2nd being topmost, above the 1st. However what I have discovered is that (assuming the underlying workbook is minimized to start) restoring the underlying workbook initially has no effect on either of the 2 userforms, but re-minimizing the workbook also takes the 2nd userform with it (i.e. minimized). I can only restore the 2nd userform to the desktop by restoring the workbook. I will admit that the userform_activate code for both the 1st and 2nd userforms also repositions each form over its parent application window using the SetWindowPlacement API: The 1st is positioned over the current foreground application window, and the 2nd user form is place over the 1st. This code uses a sequence of APIs to get the location of the "parent" window, its rectangular position and then to calculate how to place the 2nd window over the center of the current (for the 1st window) and the 1st for the 2nd window.

I have tried adding code to the workbook Workbook_WindowResize to bring the 2nd userform back to the foreground by using the command "AppActivate Userform2.Caption"). However, this command errors unless the workbook is restored (xlNormal or xlMaximised),

This behavior seems counterintuitive with respect to the expected behavior of modeless userforms. Certainly Userform1 is behaving exactly as I would expect remaining visible and on top regardless of the state of the underlying workbook window. It's the 2nd userform, launched from a command button of the 1st and also showing as modeless only staying as truly modeless until the underlying workbook window is maximized and subsequently following the state of the workbook window. Modeless userforms show be independent of the workbook containing the macros that launch them. Remember that the 1st userform continues to remain visible and always on top. It's juts the 2nd that has the problem.

I hope someone know why this is happening and can offer a solution.

Many Thanks,

 

[Jaafar Tribak]

If you are still looking for an answer, you may want to try this solution which should keep ALL USERFORMS always on top and won't minimize with the application window.

Workbook Example

PREVIEW

https://s9.gifyu.com/images/HHHAAA.gif

1- Place this code in a Standard Module:

Option Explicit

#If VBA7 Then
    #If Win64 Then
        Private Declare PtrSafe Function SetWindowLong Lib "user32" Alias "SetWindowLongPtrA" (ByVal hwnd As LongPtr, ByVal nIndex As Long, ByVal dwNewLong As LongPtr) As LongPtr
        Private Declare PtrSafe Function GetWindowLong Lib "user32" Alias "GetWindowLongPtrA" (ByVal hwnd As LongPtr, ByVal nIndex As Long) As LongPtr
    #Else
        Private Declare PtrSafe Function SetWindowLong Lib "user32" Alias "SetWindowLongA" (ByVal hwnd As LongPtr, ByVal nIndex As Long, ByVal dwNewLong As Long) As LongPtr
        Private Declare PtrSafe Function GetWindowLong Lib "user32" Alias "GetWindowLongA" (ByVal hwnd As LongPtr, ByVal nIndex As Long) As LongPtr
    #End If
    Private Declare PtrSafe Function IUnknown_GetWindow Lib "shlwapi" Alias "#172" (ByVal pIUnk As IUnknown, ByVal hUf As LongPtr) As Long
    Private 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
    Private Declare PtrSafe Function GetDesktopWindow Lib "user32" () As LongPtr
#Else
    Private Declare Function SetWindowLong Lib "user32" Alias "SetWindowLongA" (ByVal hwnd As Long, ByVal nIndex As Long, ByVal dwNewLong As Long) As Long
    Private Declare Function GetWindowLong Lib "user32" Alias "GetWindowLongA" (ByVal hwnd As Long, ByVal nIndex As Long) As Long
    Private Declare Function IUnknown_GetWindow Lib "shlwapi" Alias "#172" (ByVal pIUnk As IUnknown, ByVal hUf 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 Declare Function GetDesktopWindow Lib "user32" () As Long
#End If


Public Property Let AlwaysOnTop( _
    ByVal oForm As Object, _
    ByVal OnTop As Boolean _
)

    Const HWND_TOPMOST = -1
    Const HWND_NOTOPMOST = -2
    Const SWP_NOSIZE = &H1
    Const SWP_NOMOVE = &H2
    Const SWP_SHOWWINDOW = &H40
    Const GWL_HWNDPARENT = (-8)
    Const GWL_EXSTYLE = (-20)
    Const WS_EX_APPWINDOW = &H40000

    #If Win64 Then
        Dim hwnd As LongLong, hOwnerWindow As LongLong
    #Else
        Dim hwnd As Long, hOwnerWindow As Long
    #End If
    Dim lExStyles As Long, lTopMost As Long
   
    Call IUnknown_GetWindow(oForm, VarPtr(hwnd))
   
    lExStyles = GetWindowLong(hwnd, GWL_EXSTYLE)
   
    lExStyles = IIf(OnTop, lExStyles And Not WS_EX_APPWINDOW, lExStyles Or WS_EX_APPWINDOW)
    lTopMost = IIf(OnTop, HWND_TOPMOST, HWND_NOTOPMOST)
    hOwnerWindow = IIf(OnTop, GetDesktopWindow, Application.hwnd)
   
    Call SetWindowLong(hwnd, GWL_EXSTYLE, lExStyles)
    Call SetWindowPos(hwnd, lTopMost, 0, 0, 0, 0, SWP_NOMOVE Or SWP_NOSIZE Or SWP_SHOWWINDOW)
    Call SetWindowLong(hwnd, GWL_HWNDPARENT, hOwnerWindow)

End Property

2- Code Usage in each UserForm Module:

Option Explicit

Private Sub UserForm_Initialize()
    AlwaysOnTop(Me) = True
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    AlwaysOnTop(Me) = False
End Sub

 

[Mrock1]

Thanks for that. Your code is essentially the same as mine except I don’t use a subroutine called from the _initialize routine - I have the code explicitly for each form.

I think the problem is an undocumented excel feature and the only workaround is to redisplqy the form whenever the workbook windows state changes. It is interesting that the primary userform works just fine and independently of the workbook window and it’s only the sensory userforms that want to minimise with the workbook window. All good and thanks for your ideas.

 

[Jaafar Tribak]

Thanks for that. Your code is essentially the same as mine except I don’t use a subroutine called from the _initialize routine - I have the code explicitly for each form.

I think the problem is an undocumented excel feature and the only workaround is to redisplqy the form whenever the workbook windows state changes. It is interesting that the primary userform works just fine and independently of the workbook window and it’s only the sensory userforms that want to minimise with the workbook window. All good and thanks for your ideas.

With the code I posted, all userforms stay on top even when minimizing the workbook\application... I thought that was what you wanted.