Hide all running applications when excel loads

K1600

Board Regular
Joined
Oct 20, 2017
Messages
70
I have the following code which runs when my file is opened. It hides excel from view, checks if Outlook is open (MsgBox if not) and then loads my logon userform. I am having an issue that if the Outlook MsgBox is displayed, when OK is clicked and the MsgBox closes the logon userform is hidden behind other open windows on the desktop.

I think it would be better if I could minimise all applications (similar to pressing the windows key and D) meaning that only my MsgBox's &/or userform will be on display. I have tried a few different bits of code to do this however, most hide all the applications but then when a user logs in, the next userform doesn't show and excel is still running in the background but is hidden so I can't get it back without closing it in task manager.

Any help would be greatly appreciated.

Current code on opening is:
VBA Code:
Private Sub Workbook_Open()
            
'Launch Excel in maximised window. This will hopefully make the logon screen open centred
    Application.WindowState = xlMaximized
    
'Hide excel after launch so only userform displays
    Application.Visible = False             'Add this to hide excel when UserForm loads

'Checks if outlook is open
Dim oOutlook As Object
Dim answer As VbMsgBoxResult

    On Error Resume Next
    Set oOutlook = GetObject(, "Outlook.Application")
    On Error GoTo 0

    If oOutlook Is Nothing Then
        MsgBox "Please open Microsoft Outlook (e-mail application)." & vbCr & _
            "You will need this open to use this system." & vbCr & _
            "" & vbCr & _
            "NOTE: Your mailbox must be capable of sending an e-mail" & vbCr & _
            "             i.e. not be full beyond your quota." & vbCr & _
            "" & vbCr & _
            "             If outlook cannot send e-mails then your actions" & vbCr & _
            "             on this system will not be completed correctly!", vbOKOnly
    Else
    End If

'Loads UserForm immediatly on file being opened
    UsrFrmAdminLogin.Show vbModal

End Sub

I tried things such as this which works to hide everything but then when I click any command button at all on my login userform I just get a blank screen and excel is hidden:
Code:
    CreateObject("Shell.Application").MinimizeAll
    Application.Windows(ThisWorkbook.Name).WindowState = xlNormal

And also:
Code:
    CreateObject("Shell.Application").MinimizeAll
    Application.Wait (Now + TimeValue("0:00:02"))
    Application.Windows(ThisWorkbook.Name).WindowState = xlNormal


Thanks in advance.
 

Some videos you may like

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
L

Legacy 456155

Guest
Search for 'VBA TOPMOST SetWindowPosition' in your favorite search engine. That might be the easiest solution.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,032
Messages
5,545,626
Members
410,696
Latest member
JTrehan
Top