Sometimes, you may want to save a workbook when a specific cell is changed or range of cells is changed. This macro allows you to define the range of cells that, when changed, forces the workbook to save.
Saving a Workbook When a Specific Cell Is Changed
'------------------ Worksheet ------------------ Private Sub Worksheet_Change(ByVal Target As Range) 'Step 1: Does the changed range intersect specified range? If Intersect(Target, Range("A5:A10")) 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
How This Macro 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 (A5:A10 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.
- 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.
- 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, 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.
Most VBA code should be placed in Standard Modules unless specified.
If you see a comment
'------------------ Modules------------------ in the code header that means put the code in a Standard Module. For more information, learn this course: Where should I put the Excel VBA code?
The following steps teach you how to put VBA code into a Standard Module:
- Activate the Visual Basic Editor by pressing ALT + F11.
- Right-click the project/workbook name in the Project Window.
- Choose Insert -> Module.
- Type or paste the code in the newly created module. You will probably need to change the sheet name, the range address, and the save location.
- Click Run button on the Visual Basic Editor toolbar.
- For more information, learn this course: Programming with Excel VBA