Excel Workbook Level VBA Change Event

The following Excel VBA article will trap a change in every sheet on your workbook.  Rather than putting a worksheet change event on each sheet, this procedure should be put in the Thisworkbook module.  This assumes all sheets in the workbook are templates and you want to trap a change and perform the same action on each sheet when this change occurs.  

The ThisWorkbook Module

The ThisWorkbook is the global workbook module which can be used to run procedures that a workbook wide. ThisWorkbook always refers to all of the worksheet modules in the workbook so can be seen as a module for global procedures.

This property can be found at the bottom of all the worksheets Microsoft Excel. In the following example there is one sheet in the workbook called Picture. The ThisWorkbook sits below that sheet module.

This Workbook Module
 

The ThisWorkbook Module

The following is a very simple procedure which will turn the cell red when there has been a change in cells A10:A15. So changing those cells results in an instant change in the cell colour of

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Not Intersect(Target, Range("A10:A15")) Is Nothing Then 'VBA change event for all sheets
Target.Interior.Color = vbRed
End If
End Sub

The Excel VBA procedure in the workbook below shows the workings.