Unexpected workbook display when executing workbook.close while application.visible=false

Question

I have an unexpected display when closing a new workbook !

I launch my macro from the command line.
Application is made invisible from workbook_open :

Sub workbook_open()

    Application.Visible = False
    UserForm1.Show
    Application.Visible = True

End Sub

UserForm1 contains a single button.
-> Only the userform is displayed.

Pressing the button starts the following code :

Sub UnexpectedDisplay()

    Dim NewBook As Workbook
    Debug.Print "_______________"

    Application.Visible = False
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False

    Set NewBook = Workbooks.Add

    With NewBook
        .Title = "MyTitle"
        .Subject = "Display"
        .SaveAs Filename:="MyWorkbook"
    End With

    Debug.Print ActiveWorkbook.Name
    MsgBox "New workbook added"

    NewBook.Close SaveChanges:=True

    Debug.Print ActiveWorkbook.Name
    MsgBox "Workbook closed"

    Application.DisplayAlerts = True

End Sub

The first msgbox is displayed : "New workbook added"
-> Only the msgbox is displayed.

Press "Ok"

The second msgbox is displayed : "Workbook closed"
Debug Trace :

_______________   
MyWorkbook.xlsx   
MyMacroFile.xlsm

-> A workbook becomes visible
( when MyMacroFile.xlsm is opened?).

-> Question : How to avoid this.

Where is the code for opening the workbook? Does this button in question close the form, as the next line in workbook_open is application.visible=true. It looks like it's right, as the activeworkbook will be the one calling the code, you are closing newworkbook, so put the same before opening and see what it is then? Does the other workbook itself open another further workbook. — Nathan_Sav
The Workbooks.Add method makes the new workbook the active one. It will be assigned the ActiveWindow where it will be come visible. To avoid that set Application.ScreenUpdating = False, Add the new workbook, hide the ActiveWindow, and re-enable Screenupdating. The previously ActiveWindow will be displayed. — Variatus
@Nathan_Sav : The button does not close the form. It only launches the UnexpectedDisplay macro. You press the userform cross to quit the application. — BisBee
@Nathan_Sav : "Does the other workbook itself open another further workbook. " ; NewBook opens no more workbook. — BisBee
@Variatus : Application.ScreenUpdating = False is lost when Workbooks.Add is executed? — BisBee

Variatus Variatus · Accepted Answer · 2020-02-26T01:29:13

Please try this code.

Sub TestInVis()

    Dim Wb As Workbook
    Dim Fn As String

    Fn = FileOpenName("Test")
    Application.ScreenUpdating = False
    Set Wb = Workbooks.Open(Fn)
    Wb.Windows(1).Visible = False
    Application.ScreenUpdating = True
End Sub

Before doing so, please set the variable Fn to contain a valid filename, complete with its path. (I used a function called FileOpenName which calls a FilePicker dialog.) The code will open the specified workbook and hide its window, leaving the previously displayed window on top.

In my test, the Open action didn't re-instate Screen Updating but I'm not sure that disabling it makes a difference. There is a short flicker while the workbook is being opened and I thought it was less pronounced while ScreenUpdating = False. It will be prolonged, however, if the file is large. One way or the other, the file opens invisibly and you can access it by the object variable to which it was assigned.

Next, I tested the same procedure with Workbooks.Add. All I said about about Open applies equally, except that the new workbook is very small, of course. Therefore it's even harder to tell whether ScreenUpdating really makes a difference.

Unexpected workbook display when executing workbook.close while application.visible=false

1 Answers