CODEWITHSUNDEEP
excelvba
Mondee
Mondee

Reputation: 29

Hide only specific workbook without affecting other workbook

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

Upvotes: 0

Views: 2954

Answers (1)

Pᴇʜ
Pᴇʜ

Reputation: 57683

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

Upvotes: 2

Related Questions