Workbook Closed by VBA Doesn't Close Referenced, IsAddin Workbook

Question

Version

Excel 2016 Desktop

Context

A VBA project ("client" .xlsb) has a reference to another workbook (Tools menu > References). The referenced book isn't an addin, it's a normal .xlsb FILE.

This has the effect that the referenced book cannot be closed if the client book is still open:

"This workbook is currently referenced by another workbook and cannot be closed."

I desire that behavior.

Problem

If the client book is closed by VBA using ThisWorkbook.Close, then the referenced book remains in memory-- it doesn't get automatically closed, as it does when the client is manually closed.

How to make the referenced book close automatically, when the Client book is closed with VBA? Ideally, the solution would close the referenced book even if other workbooks are open.

i wouldn't be able to use a solution which requires programmatically removing the reference from the client, as i can't guarantee that users have trusted access to VBA, in Macro Security.

Assume that more than one open book may reference the referenced-book, so the reference shouldn't be closed until the last client is closed.

Attempted Solutions

• AfterClose event: Let the referenced book close itself, when no other clients are open. The challenge here is that the referenced file can't be closed until after all clients are closed. Which means, we have to wait for the Workbook_AfterClose event -- unfortunately no such Workbook_AfterClose event exists. i attempted to roll my own Workbook_AfterClose event by intercepting the Application_WorkbookDeactivate and Application_WorkbookActivate events (external to the client). No luck so far.

• OnTime: Emulate an AfterClose event with an OnTime event 5 seconds after the last client closes. However, it appears that OnTime events aren't fired if they are registered in a Workbook_Close event that was initiated by VBA with ClientWorkbook.Close.

• Emulate mouse: In the client book's Workbook_Close event, emulate a mouse click on the client book's front-end close-button. But manipulating the mouse seems generally a bad idea. Also, this won't close the referenced-book if other unrelated books are open.

• Remove reference: Programmatically remove the reference before closing the client. But that requires trusted access to VBA, which i don't have.

Answer 1

This is what you are facing

Is this what you are trying?

Option Explicit

'~~> This is the name of the VBA project from the referenced workbook
Private Const ReferenceWbModule As String = "MyVBAProject"
Private Const ReferenceWb As String = "Test.xlsb"

Sub Sample()       
    '~~> Save the workbook (IMPORTANT)
    ThisWorkbook.Save
    
    '~~> Remove the reference
    Dim RefName As String: RefName = ReferenceWbModule
    Dim oRefS As Object, oRef As Object
    Set oRefS = ThisWorkbook.VBProject.References
    For Each oRef In oRefS
        If oRef.Name = RefName Then
            oRefS.Remove oRef
            Exit For
        End If
    Next
    
    '~~> Close the workbook
    Dim wb As Workbook
    Set wb = Workbooks(ReferenceWb)
    wb.Close (False)
    
    '~~> Close without saving so that next time the reference is still there
    ThisWorkbook.Close (False)
End Sub

And this is what happens now

NOTE

You will have to give Programmatic access to Office VBA project

Else you will get the below error when you run the code.

Answer 2

Thanks to Naresh, i see how to execute OnTime in a programmatic workbook close event. i improved on that method, by putting the dirty details into the external book.

This solution requires the client-book to call a custom CloseBook procedure, instead of ThisWorkbook.Close but that's not terrible.

Client Book

Module 1

Sub Test()
          CloseBook ThisWorkbook
End Sub

Notes

• Reference: If the event listener is loaded into references, then you can use the above syntax-- call CloseBook as if it's internal to the Client Book.
• Addin: If the event listener is an addin, or just another workbook, then you need to use the Run syntax: Application.Run "CloseBook", ThisWorkbook

Event Listener Book

Module 1

Sub CloseBook(oBk As Workbook)
          ' This is the secret sauce. 
          ' The OnTime procedure won't execute until this sub finishes, 
          ' which won't happen until oBk.Close finishes. 
          ' That's what makes this an AfterClose event. 
          Application.OnTime Now, "Workbook_AfterClose"
          oBk.Close
End Sub


Sub Workbook_AfterClose()
          ' This is the AfterClose event. Put anything you want here.
          If NoClients Then ThisWorkbook.Close
End Sub

---

(Note, the NoClients procedure is outside the scope of this question)