Playing with Excel Worksheet Change event

Workbook sheet change is a very good place to put your code if you want to track user actions. Depending on user action, you may want to write validations or database round trips. It can also be a substitute for "formulas" if written correctly. But it is easy to overlook scenarios and user interaction patterns, and make mistakes in coding.

A good way to begin is by creating sub routine to manage your operations on the Excel sheet. This is a global sub that can be called to set when some "controlled" system operations are going on. (System = your code).

The following code can be placed in a public module.

Option Explicit

Public bSystemUpdating As Boolean, oLastCalulation As XlCalculation
'Flag to indicate start of system update

Public Sub ManageSystemUpdate(bStartUpdate As Boolean)
If bStartUpdate Then
    
    If bSystemUpdating Then Exit Sub
    'Exit if update by system already started
    
    bSystemUpdating = True
    oLastCalulation = Application.Calculation
    ' Store Calculation mode for reseting when done
    
    Application.Calculation = xlCalculationManual
    
Else

    Application.Calculation = oLastCalulation
    bSystemUpdating = False
    
End If

Application.EnableEvents = Not bStartUpdate
'To disable Excel events like Worksheet change

Application.DisplayAlerts = Not bStartUpdate
'To disable Excel alerts like "Delete Sheet confirmation" etc

Application.ScreenUpdating = Not bStartUpdate
'To disable Excel screen updatation... Excel will update the screen on completion of your routine

End Sub

What next? Now we take the "Workbook_SheetChange" event and add our custom code into it. Remember, the "Workbook_SheetChange" event gets fired everytime any change happens to the cells, i.e., including your code. So it is important to manage the re-firing of these events in case you make updates. This can be done by setting a global variable to flag the start of a system update.

Option Explicit

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Not Application.Intersect(Application.Range("Sheet1!A:A"), Target) Is Nothing Then
    'Only if any cell in the column "A" changes
    
    FetchFooValues Target
    
End If
End Sub

Public Sub FetchFooValues(oRange As Range)
Dim lCtr As Long

    ManageSystemUpdate True
    'Disable all events as well as all updates and calculation by Excel application
    
    For lCtr = 1 To oRange.Rows.Count
        Application.Range("Sheet1!B1").Offset(lCtr - 1).Value = FetchSingleFooValue(Trim(Application.Range("Sheet1!B1").Offset(lCtr - 1).Value))
    Next
    
    ManageSystemUpdate False
    'Enable all events
    
End Sub

Private Function FetchSingleFooValue(sInput As String)

'Do some operation here
FetchSingleFooValue = sInput

End Function

All done!