Excel Macro Workbook_BeforeClose Specific Workbook

Question

I have two workbooks, one that we enter data in (Workbook A), and one we use for calculation (Workbook B).

When Workbook B is closed the following code runs, to reset it...

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Range("B2:B4,B6:B8,B10:B12,B14:B16,B18:B20,B22:B24,B26:B28,B30:B32").ClearContents
    Range("B2").Select
    ActiveWorkbook.Save
End Sub

The problem is that if someone exits Excel while Workbook B is open, but Workbook A is in the foreground, you receive a Run-time error '1004'.

How can I change the macro to run the above code on Workbook B, regardless of what other workbooks are open or in the foreground?

Thank you in advance for any help.

Unknown Unknown · Accepted Answer · 2017-05-06T18:51:33

You've provided no indication what worksheet the range is on. Perhaps,

Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Cancel = False
    With ThisWorkbook.Worksheets("sheet1")
        .Range("B2:B4,B6:B8,B10:B12,B14:B16,B18:B20,B22:B24,B26:B28,B30:B32").ClearContents
        .Range("B2").Name = "openHere"
        Application.Goto Reference:="openHere"
        .Parent.Save
    End With
End Sub

You have to activate a worksheet in order to .Select a cell on it but Application.Goto can select a named cell. I've adjusted the code above to use a worbook-scope defined name and then Application.Goto that cell.

Excel Macro Workbook_BeforeClose Specific Workbook

1 Answers