Using Macros to Save Workbooks when Cells Change

By Michael Alexander

In Excel, you may be working on data that is so sensitive that you’ll want to save every time a particular cell or range of cells is changed. This macro allows you to define a range of cells that, when changed, forces the workbook to save.

In the example shown, you want the workbook to save when an edit is made to any of the cells in the range C5:C16.

Changing a cell in C5:C16 forces the workbook to save.
Changing a cell in C5:C16 forces the workbook to save.

How the macro works

The secret to this code is the Intersect method. Because you don’t want to save the worksheet when any old cell changes, you use the Intersect method to determine if the target cell (the cell that changed) intersects with the range specified as the trigger range (C5:C16 in this case).

The Intersect method returns one of two things: a Range object that defines the intersection between the two given ranges, or nothing. So in essence, you need to throw the target cell against the Intersect method to check for a value of Nothing. At that point, you can decide whether to save the workbook.

Private Sub Worksheet_Change(ByVal Target As Range)
'Step 1: Does the changed range intersect?
    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
'Step 4: Close out the If statement
    End If
End Sub

In Step 1, you simply check to see whether 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 is outside the range specified.

Step 2 forces the macro to stop and exit the procedure if there is no intersection between the target cell and the specified range.

If there is an intersection, Step 3 fires the Save method of the active workbook, overwriting the previous version.

In Step 4, you simply close out the If statement. Every time you start an If-Then-Else check, you must close it out with a corresponding End If.

How to use the macro

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 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 to see all the sheets.

  3. Click the sheet from which you want to trigger the code.

  4. In the Event drop-down list, select the Change event.

  5. Type or paste the code in the newly created module, changing the range address to suit your needs.

    Enter your code in the Worksheet Change event.
    Enter your code in the Worksheet Change event.