How to make vba code to run auto-close only on active workbook?

Question

Here's the scenario. I have multiple excel workbooks that copy and paste data among each other. So the macro works to open.copy.close from one workbook then open.paste.close to another. I am working on creating a function to auto run macro when file is closed.

Here's the issue, when I click macro button in workbook 1, it is supposed to open.copy.close from workbook 2. However, because of the auto run when file is closed function in workbook 2, an error will occur (2 macros cannot run at the same time)Any solution for this? I am looking for a solution to only auto run macro when file is closed IF IT IS AN ACTIVE WORKBOOK. Here is what I have now:

Workbook 1
Sub workbook_beforeclose(cancel As Boolean)
Application.Run "Sheet1.UpdateYellowTabs_Click"

End Sub

Workbook 2
Sub Workbook_BeforeClose(Cancel As Boolean)
Workbook.BeforeClose
Application.Run "Sheet12.UpdateGreen_Click"
End Sub

How do I code it in the workbook code to only make this run only when it's active/closed by a human user and not when open/close by macro?

Thanks!

A.S.H A.S.H · Accepted Answer · 2017-06-11T17:24:54

Well I am not sure to understand your final goal from this, but I can answer the "technical" question. Technically, if you want to check if a given workbook is active: If Application.ActiveWorkbook Is Me,

Private Sub Workbook_BeforeClose(Cancel As Boolean)
  If Not Application.ActiveWorkbook Is Me Then Exit Sub ' <-- add this test

  ''''''''''''''''''''''''''
  ' Whatever code goes here
  ''''''''''''''''''''''''''
End Sub

EDIT

But problem is that invoking wb2.close will make the workbook wb2 the "active" one during the execution of the macro. So this method won't work. Another method is to disable events before closing the workbook; so that the event Workbook_BeforeClose will not fire. After that, you can enable events back if needed. The code looks like this:

' ... open wb2 and do the work with it
Application.EnableEvents = False
wb2.Close False
Application.EnableEvents = True

notice, if you had already disabled events at the beginning of the current macro, which is usually recommended, then this additional code wouldn't be needed.

How to make vba code to run auto-close only on active workbook?

1 Answers