Show modeless form on top of open workbooks.

[OaklandJim]

Team Mr. XLNT. Excel 365 64 bit. I am not a "power" VBAer.

I know that this has been addressed before but I just did not understand how to implement "solutions" that I found.

I have a modeless form that keeps user posted about progress. Works great...except the form is in the "Master" workbook which opens a "Target" workbook to take some actions on the Target workbook. So, the Form shows over the Master workbook but it is covered up by the Target workbook that is being processed. I tried minimizing the Target workbook, and activating the Master but I could not get the form to show "on top" of the Target workbook.

I'm sure that there is a good explanation of the way to get that form "on top"? I am also surprised that there is not a straightforward way to do this.

Jim

 

[Logit]

Try this:

Option Explicit

'Written By RobDog888 - VB/Office Guru™
'Add a Command Button so you can toggle the userform's topmost effect
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

Private Sub CommandButton1_Click()
    If CommandButton1.Caption = "Not Topmost" Then
        SetWindowPos mlHwnd, HWND_NOTOPMOST, 0, 0, 0, 0, SWP_NOMOVE Or SWP_NOSIZE
        CommandButton1.Caption = "Topmost"
    Else
        SetWindowPos mlHwnd, HWND_TOPMOST, 0, 0, 0, 0, SWP_NOMOVE Or SWP_NOSIZE
        CommandButton1.Caption = "Not Topmost"
    End If
End Sub

Private Sub UserForm_Initialize()
    mlHwnd = FindWindow("ThunderDFrame", "UserForm1") 'Change to match your userforms caption
    Do While mlHwnd = 0
        mlHwnd = FindWindow("ThunderDFrame", "UserForm1") 'Change to match your userforms caption
        DoEvents
    Loop
    'Set topmost
    SetWindowPos mlHwnd, HWND_TOPMOST, 0, 0, 0, 0, SWP_NOMOVE Or SWP_NOSIZE
    CommandButton1.Caption = "Not Topmost"
    
'''''''''''
'''''''''''
'added following line to make UserForm max TopMost
'Minimize Excel's main window if you want to present the UserForm as a standard Form not "associated" with Excel.
    Application.WindowState = xlMinimized

End Sub

 

[OaklandJim]

Hello Logit. Thanks for the code. Honestly, I am not sure what to do with it. Like I said, not a power user.

I vaguely recall that for 64 bit, in Declare statements I need to add some additional code? I do remember PtrSafe?

If you could give just a bit more explanation I'd really appreciate it. E.g., Where do code elements go? All in the form?

FYI, my form is named CentersProgressDialog.

I do not need a command button and I do not want the user to have to click on one. Why do I have to add one to the form to do this? My form has two labels. That is all.

This was the last thing I added to my Master workbook so I guess I could just blow this thing out if I cannot understand/figure out what to do to make the form be on top. Oy.

Thanks again. I really do appreciate your help!

 

[Logit]

Try this version :

Top Most Form.xlsm