Hide all running applications when excel loads

K1600

Board Regular
Joined
Oct 20, 2017
Messages
181
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.
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
Search for 'VBA TOPMOST SetWindowPosition' in your favorite search engine. That might be the easiest solution.
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,981
Members
448,538
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top