How to close workbook in the background

[Ludwig]

I have a workbook which runs macros on the active workbook i.e. showing on the screen. When the macro finishes, I then close it. All works well, except the workbook with the macros flashes briefly on the screen before closing and then Excel shows the other (my 'active') workbook once more.

At the end of the macro I have coded these three VBA lines, where variable MyWorkBookName contains the name of the workbook with the macros in it. The workbook has already been saved so there are no unsaved changes at this point of the logic.
But the workbook (in string variable MyWorkBookName) still flashes briefly on the screen. -

            Application.ScreenUpdating = False
            Workbooks(MyWorkBookName).Close     ' get rid of me & my macros from this Excel environment ...
            Application.ScreenUpdating = True

Is there any way to avoid this flashing, or is it because I'm closing the workbook with the macros that are being run at the time and is unavoidable?

 

[6StringJazzer]

I'm not clear on your scenario. Is the workbook with the macro in a hidden window? Or minimized? Or just behind by other windows? If it is not visible, I am not sure why it would appear briefly before closing. However, your way of closing it is a bit indirect. I would try this to see if it makes any difference:

ThisWorkbook.Close

It's just a wild guess but it's easy to try.

 

[Ludwig]

Background:
I have a workbook A, with an external data connection that I ave deliberately set to not refresh at open. If I want to refresh it I could just use Ctrl-Alt-F5 to refresh it and all the pivot tables & formulas that reference that data, but I also refresh the conditional formatting (Excel messes them up and creates multiple copies over time) .

So I open another workbook B which has all the necessary macros, that opens the workbook A and makes it the active one i.e. the one you see on screen. The macros then cause the external data in Workbook A to refresh (& the pivot tables, etc), then goes through the worksheets in workbook A and replaces all the conditional formatting to make that tidy.

This happens while a specific worksheet from Workbook A is displayed on the screen i.e. Workbook B is I presume 'hidden' behind it.

The last thing Workbook B macros do is throw up a msgbox to say "close me or leave me open"?" If I say "close me" I want it to close without becoming the window that shows on the screen be it briefly. It's just cosmetic really, I'd just like it to be a nice 'non-presentation' close of the macros workbook.

Trying your suggestion:
I tried ThisWorkbook.Close but it makes no difference (Thisworkbook would be referencing the same as the named workbook anyway) - with or without the Application.ScreenUpdating false / true statements around it

 

[Jaafar Tribak]

Place the MsgBox code in workbook A and call the macro from Workbook B via the Run Method.

 

[Alex Blakenburg]

Does this stop the issue ?

    ThisWorkbook.Windows(1).Visible = False
    ThisWorkbook.Saved = True
    ThisWorkbook.Close

 

[Ludwig]

Does this stop the issue ?

    ThisWorkbook.Windows(1).Visible = False
    ThisWorkbook.Saved = True
    ThisWorkbook.Close

Yes, that works! Only extra thing then needed is to set visible to True in Workbook.open. Seems Excel remembers that state between close and open.

 

[Alex Blakenburg]

You shouldn't need to, the setting is saved when the workbook is saved and I am NOT saving the workbook.
Do you have autosave turned on ?

 

[Ludwig]

Place the MsgBox code in workbook A and call the macro from Workbook B via the Run Method.

I forgot to mention ... Workbook B is a standard XLSX file, I deliberately stored all macros in Workbook A so I can open Workbook B on my mobile to enter some updates.

 

[Ludwig]

You shouldn't need to, the setting is saved when the workbook is saved and I am NOT saving the workbook.
Do you have autosave turned on ?

The file is stored on Onedrive so Autosave will be turned on. Should I turn it off to avoid doing the extra at open?

 

[Alex Blakenburg]

I believe that turning off AutoSave will impact all your workbooks, so if you use it you probably won't want to turn it off.
It is also likely that other users will have it turned on. So you if you want to use the Windows hidden option you will need to unhide it when you open it.