CODEWITHSUNDEEP
vbaexceluserform
Blake Turner
Blake Turner

Reputation: 151

Bring Userform to Forefront Excel VBA

I have a userform pop up in Excel after a certain amount of time of inactivity. The only problem with this is that the userform is not visible until excel is clicked, so by the time the user would see it, another sub runs that will close the sheet once the sheet is clicked. I'm not sure how it is related but I believe the fact that I have 3 screens is interfering a bit.

Bottom line: How do I make a userform appear in front of all other active windows

Some unnecessary background:

The sheet is in a shared folder at work. The reason for this routine of the inactivity userform popping up and another routine closing excel is because only one person can make and save changes at a time. This routine will boot out the user if they have been inactive for 5 minutes, and currently creates a save-as version with the title autosave and the date/time.

Upvotes: 2

Views: 11742

Answers (1)

Patrick Lepelletier
Patrick Lepelletier

Reputation: 1654

i had (again) this issue. On an older time (same excel version) i only needed to setfocus to a control of the form and then invisible and visible again, but (why O why) this doesn't work on my new Form.

so i searched a bit and found this.

In Userform_Initialize :

With ThisWorkbook
        '.Windows(1).WindowState = xlMinimized 'workbook minimize, not needed
        '.VBProject.VBE.MainWindow.WindowState = vbext_ws_Minimize 'VBE minimize , not needed
        'SetFormParent Me, FORM_PARENT_NONE 'makes the userform independantfrom workbooks
        TopMostForm Me, True 'forces userform to show on top at all times
        'DoEvents
        TopMostForm Me, False 'Userform uses normal Zorder again            
  End With

Most of the code you don't need but i wanted to show you that it works independantly that a Excel window is present or a VBE window.

You can find the procedures for TopMostForm and SetFormParent by using Google, but here they are (64 bit).

Sub TopMostForm(F As MSForms.UserForm, Top As Boolean)
'  Makes a form the top window if top = True.  When top = False it removes  this property.
   Dim hwnd As LongPtr
   hwnd = HWndOfUserForm(F)
   If Top Then
      SetWindowPos hwnd, HWND_TOPMOST, 0, 0, 0, 0, SWP_NOMOVE + SWP_NOSIZE
   Else
      SetWindowPos hwnd, 0, 0, 0, 0, 0, SWP_NOMOVE + SWP_NOSIZE
   End If
End Sub


Function SetFormParent(Uf As MSForms.UserForm, _
Parent As FORM_PARENT_WINDOW_TYPE, Optional w As Window) As Boolean  ' mettre ,2

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' SetFormParent
' Set the UserForm UF as a child of (1) the Application, (2) the
' Excel ActiveWindow, or (3) no parent. Returns TRUE if successful
' or FALSE if unsuccessful.
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Dim UFHWnd As LongPtr
Dim WindHWnd As LongPtr
Dim R As LongPtr

If w Is Nothing Then Set w = Application.ActiveWindow
UFHWnd = HWndOfUserForm(Uf)
If UFHWnd = 0 Then
    SetFormParent = False
    Exit Function
End If

Select Case Parent
    Case FORM_PARENT_APPLICATION
        R = SetParent(UFHWnd, Application.hwnd)
    Case FORM_PARENT_NONE
        R = SetParent(UFHWnd, 0&)
    Case FORM_PARENT_WINDOW
        If w Is Nothing Then
            SetFormParent = False
            Exit Function
        End If
        WindHWnd = WindowHWnd(w)
        If WindHWnd = 0 Then
            SetFormParent = False
            Exit Function
        End If
        R = SetParent(UFHWnd, WindHWnd)
    Case Else
        SetFormParent = False
        Exit Function
End Select
SetFormParent = (R <> 0)

End Function

Upvotes: 1

Related Questions