Keep only this workbook minimised

[Sharid]

Hi

I want to keep a workbook called "Test"minimised. Only the userform will show, the user will use a command button on the userform to exit and close the form and workbook.
However I am having the following problems.

1. On open it minimise and shows the userform3, however if i click the excel icon in the task bar it opens, this should not happen.
2. When I re-minimise the workbook it hide the user form, this should not happen
3. If I open another workbook, with this one workbook opened, the the user can see the minimised "Test" workbook. this should not happen.

Only the "Test"workbook should remain minimised any other workbooks that are opened should not be impacted on.

Private Sub Workbook_Open()
 Application.WindowState = xlMinimized
UserForm3.Show modeless
End Sub

Thanks for having a look

 

[Sharid]

Hi

I tried this, it only hid the excel icon in the task bar, when I opened another workbook the same problems happened.

ActiveWindow.WindowState = xlMinimized

 

[John_w]

This can be done with Window API functions to hide the Excel window and its taskbar icon. It's not perfect - see below - but see if it works for you.

ThisWorkbook module:

Option Explicit

Private Sub Workbook_Open()
    'Save window caption of this workbook in global variable
    ThisWorkbookCaption = Application.caption
End Sub

Private Sub Workbook_Activate()
    Hide_ThisWorkbook
End Sub

UserForm1 with a command button named 'cmdClose' to close the user form and workbook. I also have command buttons to hide and show the workbook:

Option Explicit

Private Sub cmdClose_Click()

    Unload Me
    If Workbooks.Count > 1 Then
        'If more than 1 workbook is open then save and close only this workbook
        ThisWorkbook.Close SaveChanges:=True
    Else
        'Otherwise, if only 1 workbook is open then save this workbook and close the Excel app
        ThisWorkbook.Save
        Application.Quit
    End If

End Sub


Private Sub cmdHide_Click()
    Hide_ThisWorkbook
End Sub


Private Sub cmdShow_Click()
    Show_ThisWorkbook
End Sub

Standard module (e.g. Module1):

Option Explicit

#If VBA7 Then
    Private Declare PtrSafe Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As LongPtr
    Private Declare PtrSafe Function ShowWindow Lib "user32" (ByVal hWnd As LongPtr, ByVal nCmdShow As Long) 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
#Else
    Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As Long
    Private Declare Function ShowWindow Lib "user32" (ByVal hWnd As Long, ByVal nCmdShow 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
#End If


Private Const SWP_SHOWWINDOW = &H40
Private Const SWP_HIDEWINDOW = &H80
Private Const SWP_NOMOVE = &H2
Private Const SWP_NOSIZE = &H1
Private Const SW_HIDE = 0
Private Const SW_SHOW = 5

Private Const HWND_TOP = 0
Private Const HWND_BOTTOM = 1
Private Const HWND_TOPMOST = -1
Private Const HWND_NOTOPMOST = -2


'Global variable containing window caption of this workbook, set in ThisWorkbook.Workbook_Open
Public ThisWorkbookCaption As String


'Click Macro for button on sheet.  Hides workbook and shows UserForm1
Public Sub Button1_Click()
    
    Dim form As UserForm1
    
    Hide_ThisWorkbook
    Set form = New UserForm1
    form.Show vbModeless

End Sub


Public Sub Hide_ThisWorkbook()

    #If VBA7 Then
        Dim hWnd As LongPtr
    #Else
        Dim hWnd As Long
    #End If
    
    hWnd = FindWindow("XLMAIN", ThisWorkbookCaption)
    If hWnd <> 0 Then
        SetWindowPos hWnd, HWND_BOTTOM, 0, 0, 0, 0, SWP_HIDEWINDOW Or SWP_NOMOVE Or SWP_NOSIZE
    End If
    
End Sub


Public Sub Show_ThisWorkbook()

    #If VBA7 Then
        Dim hWnd As LongPtr
    #Else
        Dim hWnd As Long
    #End If
    
    hWnd = FindWindow("XLMAIN", ThisWorkbookCaption)
    If hWnd <> 0 Then
        ShowWindow hWnd, SW_SHOW
    End If
    
End Sub

Button1_Click() shows how to initially hide the workbook and show the user form.
cmdHide_Click() shows how to hide the workbook.
cmdShow_Click() shows how to show the workbook.

The code works very well for me (Windows 10, Excel 2016). With the VBA editor window closed, calling Button1_Click (by clicking the command button on the sheet) hides the workbook and shows the user form. There is no taskbar icon for the workbook or the user form.

The behaviour isn't perfect; the anomolies for me occur when another workbook is opened (from a File Explorer window):

• The initial Excel 'splash' isn't shown and the other workbook seems to take longer than normal to open.
• Excel icons for both workbooks are shown in the taskbar. However, when you activate the 'userform macro' workbook, it is hidden, leaving only the other workbook visible and with a taskbar icon, and the userform still open.

 

[Jaafar Tribak]

You may find this recent thread of interest :

Display Userform in TaskBar with custom Icon and Hide Excel (mimicking a standalone application)

Hi dear excel enthusiasts, The following little project makes your excel userform look like a standalone application by hiding the main excel window and leaving only the userform with a personalised icon added to it that shows on the TaskBar instead of the native excel icon ? The code makes...

www.mrexcel.com

At least, when tested in excel 2016, the userform behaves just like a standalone application with its own custom icon showing in the taskbar and no other workbook can be opened in the same excel instance while the form is loaded.

Threre is a workbook sample you can download for testing.