EXCEL: Example: Execute User Code 1 time when sheet data changes.

[Webtest]

Esteemed Forum Participants and Lurkers:
===============================
EXCEL VBA

I have an application where I am doing a special trending function on the last 10 data points in a series. My Chart fetches data from a certain fixed area on a sheet, and I have code that is supposed to update this area when new data is added to the end of the list in columns A and B. Once this is achieved, the Chart sheet will always show the new trend line for any new data that gets added.

The problem was that the "Worksheet_Change" Event fires immediately with any instruction that changes anything on the sheet, which rapidly degenrates into a nasty endless loop. I finally came up with the following solution to execute my update code only one time when data is added to my list. The following is a simplified example just to show the basic procedure. It will count all 'changes' to Column 1.

EXECUTING USER CODE ONE TIME WHEN DATA ON SHEET CHANGES
Copyright (c) 2005  Arthur Du Rea

* Set a Global Boolean Variable in the User Module
* Initialize that variable in the Workbook_Open Event
* Set up the Worksheet_Change Event to prevent False Recursion
* Implement Code to execute

The "Workbook_Open" Event is a standard method in the "ThisWorkbook" Class Module

To create/open the "ThisWorkbook" Module:
  In the VB Window:  Menu Bar  >  View  >  Object Browser
  In the Libraries Menu Box, select "VBAProject"
  In the "Classes" pane, doubleclick the "ThisWorkbook" item
    This opens the "ThisWorkbook (Code)" window

  In the left selection box at the top of the window, select "Workbook"
  In the right selection box at the top of the window, select "Open"

The code template for "Workbook_Open" should appear in the "ThisWorkbook" Module window
=======================================================================================
Option Explicit
'Class Module:  ThisWorkbook(Code)      This is a pre-defined Excel Class Module
'
'Global variables used here are actually defined in the USER Module - not here!
'Public gb_READY as BOOLEAN   <<<< The code for this is in the top of the USER module
'
'This is the EVENT that fires when a workbook opens.
Private Sub Workbook_Open()
    'Initialize the READY flag to allow code execution on Worksheet_Change
    gb_READY = True
End Sub
=======================================================================================
Open the Sheet Module as above ... it is similar to Sheet1(SheetName)
=======================================================================================
Option Explicit
'Module:  Similar to "Sheet1(SheetName)"      This is a pre-defined Excel Module
'
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    'Do NOT Recurse this routine!
    If gb_READY Then
        'Turn the READY flag OFF
        gb_READY = False
        
        'TEST TEST TEST TEST
        MsgBox Target.Address    'Show what was changed
        'END TEST
        
        'Here is an example of only operating on certain changes

        'We only care about changes to the first column
        If Target.Column > 1 Then gb_READY = True: Exit Sub
        
        'Ready to update the code-driven area!!!
        Macro1
    End If
End Sub

=======================================================================================
The following is a regular Macro.  You can set the template by recording a Macro
=======================================================================================
Option Explicit
'Module:  Module1 (Code)    'This is a standard User defined Macro Module
'
'GENERAL DECLARATIONS AREA:    (MUST precede any Sub or Function declarations)
'Define the GLOBAL variable - it is available to other modules in the project
Public gb_READY as BOOLEAN

'Macro to Operate on the data on the sheet
Sub Macro1()
    'The next line causes a NEW Worksheet_Change Event to be executed BEFORE continuing!
    ActiveSheet.Cells(3,1).Value = ActiveSheet.Cells(3,1).Value + 1
    '    ...
    '  All code that changes the sheet goes here
    '    ...
    'We are done making ALL changes to the Sheet data
    gb_READY = True
End Sub

Good Learning and Good Programming!