Macro time again. I just love Mondays!. Today let’s look at getting Excel to save our workbook as soon as a specific cell or range of cells is changed by a user. If it is critical that a workbook is saved once a user has changed a cell(maybe it contains some sensitive information), then this is the Macro for you.
Download Macro Mondays Example Files.
What Does The Macro Do?
This Macro forces Excel to save your workbook once a cell or range of cells you specify is changed by a user.
How Does It Work?
This Macro makes use of the Intersect method and seeing as do not want the workbook to save just at anytime, we use Intersect to determine if the target cell or cells (cell or cells that changed) intersect with a range we have specified to be the ‘trigger range’.
The Intersect method returns either a Range object (that defines the intersection) or nothing. In plain terms, we throw the targets cells at the Intersect to check for a value of nothing, Excel then makes the decision or not whether to save the workbook. Ready?, let’s get started.
In this example we are going to set the Target range as C5:C6
FIRST. Open Visual Basic – by hitting F11 or Developer Tab – Visual Basic, and this Macro needs to go into the Worksheet_Change event code window, so it can run anytime you make changes to the sheet. In your Project window find the Project/Workbook Name and then navigate to the worksheet that you want to trigger the code. In my example it it MM13. Select the Change event from the drop down list available.
We can now start writing our code. You can see it in detail below.
Step 1. Here we are seeing if the range that was changed intersects the specified range (in our exmaple it is C5:C6).
Step 2. If there is no intersection with the specified range then Excel exits the procedure.
Step 3. If there is an intersection then the the workbook is saved. We then close out the IF statement. ( as we trigger an IF…Then….Else statement we need to close it with an End If).
Here is the code if you need to copy it.
Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range(“C5:C6”)) Is Nothing Then