Macro 2: Saving a Workbook
When a Particular Cell Is
Changed
Sometimes, you may be working on data that is so sensitive that you’ll want to save every time a par-
ticular cell or range of cells is changed. This macro allows you to define the range of cells that, when
changed, forces the workbook to save.
How it works
The secret to this code is the Intersect method. Because we don’t want to save the worksheet
when any old cell changes, we use the Intersect method to determine if the target cell (the cell
that changed) intersects with the range we have specified to be the trigger range (C5:C16 in this
case).
The Intersect method returns one of two things: either a Range object that defines the intersec-
tion between the two given ranges, or nothing. So in essence, we need to throw the target cell
against the Intersect method to check for a value of Nothing. At that point, we can make the
decision whether to save the workbook.
Private Sub Worksheet_Change(ByVal Target As Range)
‘Step 1: Does the changed range intersect specified range?
If Intersect(Target, Range(“C5:C16”)) Is Nothing Then
‘Step 2: If there is no intersection, exit procedure
Exit Sub
‘Step 3: If there is an intersection, save the workbook
Else
ActiveWorkbook.Save
‘Close out the If statement
End If
End Sub
1. In Step 1, we are simply checking to see if the target cell (the cell that has changed) is in the
range specified by the Intersect method. A value of Nothing means the target cell falls
outside the range specified.
2. Step 2 forces the macro to stop and exit the procedure if there is no intersection between
the target cell and the specified range.
3. If there is an intersection, Step 3 fires the Save method of the active workbook, overwriting
the previous version.
4. In Step 4, we simply close out the If statement. Every time you instantiate an If…Then…
Else check, you must close it out with a corresponding End If.
How to use it
To implement this macro, you need to copy and paste it into the Worksheet_Change event code
window. Placing the macro here allows it to run each time you make any change to the sheet.
1. Activate the Visual Basic Editor by pressing ALT+F11.
2. In the Project window, find your project/workbook name and click the plus sign next to it in
order to see all the sheets.
3. Click in the sheet from which you want to trigger the code.
4. Select the Change event from the Event drop-down list (see Figure 2-1).
5. Type or paste the code in the newly created module, changing the range address to suit your
needs.
Figure 2-1: Enter or Paste your code in the Worksheet_Change event code window.
