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 SubWhat 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 FunctionAll done!
Hand hold the team about what is to be done at each step - what questions are we trying to answer here? Develop your own "team jargon" that the team can connect to, for each activity.
One such area is the "Defend Meeting" that surprisingly does not figure high in these lists. Why so important? Because when it comes to planning, 50% of us are below average.