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

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.

Code:

---

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!

The following may not apply in your case, but there is a way using the Offset function to have charts update automatically when new data is entered, w/o the use of macros.

http://j-walk.com/ss/excel/usertips/tip053.htm

VBAhack

Hack ...

That's a very valuable tip! No it doesn't apply in my case ... but I will definitely put it in my reference Cookbook!

I have a static area for my chart, but the data has to change based on other data that gets entered on the sheet. I wanted my calculated data block updated with the newly entered data in my series. Sometime, I may need to combine BOTH approaches!

Thanks again!

This is a WorkSheet_Change event that updates an embedded charts data range accordingly:

VB Code:

Option Explicit 
 
Private Sub Worksheet_Change(ByVal Target As Range) 
     '   Credit to Aaron Blood as the originator of this code:
     '   [url]http://www.xl-logic.com/pages/charts.html[/url] (DynamicChart3.zip)
    Dim oChrt As ChartObject 
    Dim szSeries As String 
     
     
    On Error Goto ErrExit 
    Application.EnableEvents = False 
     
    Set oChrt = ActiveSheet.ChartObjects(1) 
     
    If Not oChrt Is Nothing Then 
         
        For Each oChrt In ChartObjects 
             
             
            szSeries = oChrt.Chart.SeriesCollection(1).Formula 
             
            szSeries = Left(szSeries, InStrRev(szSeries, ",") - 1) 
             
            szSeries = Right(szSeries, (Len(szSeries) - InStrRev(szSeries, ","))) 
             
            oChrt.Chart.SetSourceData Source:=Range(szSeries).CurrentRegion 
             
             
        Next oChrt 
         
    End If 
     
    Set oChrt = Nothing 
ErrExit: 
    Application.EnableEvents = True 
End Sub

I think the code you are using to tell if a change happened only in column 1 could be simplified more to this: as it makes the change, and avoids your endless loop issue. You could combine the above code plus this one below into one fairly easy, but if you have trouble or this isn't what you needed, post back.

VB Code:

Option Explicit
 
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column <> 1 Then Exit Sub
    
    On Error GoTo ErrorHandler
    With Application
        .EnableEvents = False
        
        With ActiveSheet
            .Cells(3, 1).Value = .Cells(3, 1).Value + 1
        End With
        
ErrorExit:
        .EnableEvents = True
    End With
    Exit Sub
    
ErrorHandler:
    MsgBox Err.Description
    Resume ErrorExit
End Sub

Thanks Justin ...

That is some good information to keep in mind and I learned a lot from your post. FYI, and for posterity, the situation changes when you have a separate worksheet for a chart ... "Sheets("chartsheetname").ChartObjects.Count" returns a ZERO (even though there is obviously a chart on the sheet!). You have to switch to "Charts()" rather than "Sheets()".

Code:

---

MsgBox ActiveWorkbook.Charts.Count                  'Shows COUNT of CHART sheets
MsgBox ActiveWorkbook.Charts(1).ChartObjects.Count  'Shows ZERO!!!
MsgBox Charts(1).Name                                'Shows my sheet name "Chart1"
MsgBox Charts("Chart1").SeriesCollection(1).Formula  'Shows "SERIES" formula

---

So, a ChartObject only refers to a Chart on a regular WorkSheet. Incidentally, the formula line will give you the SERIES formula for X/Y Scatter Plots, in case you were wondering.