How to make sure Macro workbook don't close while closing other workbooks

Question

Scenario

I have a userform whereby excel workbook will be hidden while opening using the following method of Application.Visible = False. These are the codes

My userform

show excel button is Commandbutton1
hide excel button is Commandbutton2

This workbook

Codes

Private Sub Workbook_Open()
  Call hideExcel
  UserForm1.Show
End Sub

Userform1

Codes

Private Sub CommandButton1_Click()
    If Workbooks.Count > 1 Then
        Windows(ThisWorkbook.Name).Visible = True
    Else
        Application.Visible = True
    End If
End Sub
Private Sub CommandButton2_Click()
    Call hideExcel
End Sub
Sub UserForm_Initialize()
    Call hideExcel
End Sub
Private Sub UserForm_Terminate()
    If Workbooks.Count > 1 Then
        Windows(ThisWorkbook.Name).Visible = True
    Else
        Application.Visible = True
    End If
End Sub
Sub userform_click()
    Call hideExcel
End Sub

Module

Codes

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

Problem

The problem I am facing is

Open my macro and userform activated. Lets call this file A
Then open another workbook. Lets call this file B
Tried to close file B while workbook A is hidden. But there is a prompt to close file A also and eventually all excel will be closing including my macro file which is A.

Does anyone know what is the problem here?

Piotr Piotr · Accepted Answer · 2018-10-24T11:32:57

I don't understand where the problem is? If you are closing last visible (not hidden) workbook, Excel also tries to close all other open workbooks (even if they're hidden). And I think it's normal Excel behavior. You can only avoid to see a prompt, e.g. by setting up Workbook.Saved property to True or by setting up Application.DisplayAlerts property to False or just by saving workbook before closing.

If you don't want to close hidden workbook you just have to make it visible before closing the second workbook.

How to make sure Macro workbook don't close while closing other workbooks

1 Answers