Re-using a VBA macro with "Worksheet_Change" event in all open workbooks in a single file

Question

The following VBA code snippet should be executed in all open workbooks within a single Excel file (*.xlsm):

Private Sub Worksheet_Change(ByVal Target As Range)
...
End Sub

We do not want to copy the code in each workbook to reduce code duplication.

When trying to create a new Macro via the Excel "Macro" dialog it offers the possibility to locate the Macro in:

all open workbooks
this/current workbook
current file

When choosing (1) in combination with a Macro name, e.g. "MultiSelect" Excel jumps in the VBA editor and scaffolds a basic method according to the given name:

Sub MultiSelect()
...
End Sub

Our question: how to guarantee reacting on "Worksheet_Change" events within this macro?

Following link shows section "Event Code Location" where it is mentioned that "For sheet (both worksheet and chart sheet) level events, the event procedure code must be placed in the Sheet module associated with that sheet". Finally, does it mean there is no possibility to prevent code duplication in that case? — Holger King
You can write the event handler in a separate workbook to handle an application level event and then turn that into an add-in. That is a popular choice for people in your situation. — Robin Mackenzie
Got it :) It's working :) Thanx for your help! Especially, reading Application Events In A New Class Module helped. — Holger King
Single problem: "App_SheetChange" event procedure is called twice. Can we prevent that? — Holger King

Holger King Holger King · Accepted Answer · 2017-02-14T09:58:24

With the help of "Robin Mackenzie" I found a solution :) Especially, reading section Application Events In A New Class Module helped. So, I created a new class named "CExcelEvents":

Private WithEvents App As Application

Private Sub App_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    'gets the code more robust when the SheetChange event is called twice
    If Me.EnableEvents = False Then
        Exit Sub
    End If

    Application.EnableEvents = False
    ...//code to centralize
    Application.EnableEvents = True
    Me.EnableEvents = False
End Sub

Private Sub Class_Initialize()
    'setting a variable for this object
    Me.EnableEvents = True
    Set App = Application
End Sub

and added in each worksheet - where the central code located in CExcelEvents has to be executed - the following object creation scaffold:

Private XLApp As CExcelEvents

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    Set XLApp = New CExcelEvents
    Application.EnableEvents = True
End Sub

To prevent eventloops, see: Run a macro when certain cells change in Excel but from my Personal workbook

Re-using a VBA macro with "Worksheet_Change" event in all open workbooks in a single file

1 Answers