Hide only specific workbook without affecting other workbook

Question

Already circled the internet and has the same answer that did not work as I wanted to be.

Q: How to hide workbook and show userform without hiding other workbook?

This code is good but hides other workbooks.

Application.Visible = False

This code is bad as it still shows the excel application.

Workbooks("Workbook Name.xlsm").Window(1).Visible = False

Also dont work.

ActiveWorkbook.Visible = False

Lastly, the tool method, going to tools>options>general tab>ignore other application. I dont have this option on my VBA

This is the code I used.

Private Sub UserForm_Initialize()
If Application.Windows.Count > 1 Then
Application.Windows(ThisWorkbook.Name).Visible = False
Else
Application.Visible = False
End If

and in ThisWorkbook module

Private Sub Workbook_Open()
UserForm1.Show
End Sub

You ask to hide a specific workbook, but complain it does not hide the application, but also say hiding the appkication is not good as it hides other workbooks... What di you really want? You need to be clear. — Solar Mike
as the title says, hide the only specific workbook. 1st code hides all workbook, then 2nd code did not hide the excel application. — Mondee
You state “this code is bad as it still shows the application”, therefore that does not match with your title question... — Solar Mike

Pᴇʜ Pᴇʜ · Accepted Answer · 2018-11-21T07:31:37

The correct way to hide one specific workbook is

Application.Windows(ThisWorkbook.Name).Visible = False

where ThisWorkbook.Name can be replaced with the desired workbook name like "MyWb.xlsm".

If there are more than one workbooks open at the same time this will hide the specified workbook (and its application window).
If this is the only workbook that is open it will hide the specified workbook (without its application window).

If you want to hide the application window you must use

Application.Visible = False

The trick is now to combine them

If Application.Windows.Count > 1 Then 'more than one workbook open: Hide workbook only
    Application.Windows(ThisWorkbook.Name).Visible = False
Else 'only one workbook open: Hide application
    Application.Visible = False
End If

Hide only specific workbook without affecting other workbook

1 Answers