Closing Another Workbook With VBA

[SamuelT]

Hi all,

I have a piece of VBA that opens up another workbook when the user opens a document (this is due to using INDIRECT formulae). I'd like for this second workbook to also close when the 'parent' workbook is closed. Can anyone suggest a means of doing this?

FYI - the opening VBA is as follows:

Private Sub Workbook_Open()

Dim Ans As Integer

Ans = MsgBox("This document requires the EDMS Upload Register to be open to calculate some figures. Would you like to open it now?", vbYesNo)
Select Case Ans
    Case vbYes
    Workbooks.Open "U:\Trackers\EDMS\EDMS UPLOAD REGISTER.xls"
   
    
    End Select
    

End Sub

TIA,

SamuelT

[macaulay]

Hi Samuel,

OK, two workbooks (BookA.xls and BookB.xls). When BookA is closed you want to force BookB to close (and save changes, for example)

If you add the following code to ThisWorkbook module in BookA.xls

Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Call Workbooks("BookB.xls").Close(True)
End Sub

Works for me. No doubt you will need to do some other checking etc.

Hope that helps.

cheers

Malcolm