Hello Excellers, and welcome back to #MacroMonday and another blog post in my series. Today’s tutorial will cover how to prompt Excel to save a workbook once a specific cell is changed. This macro is handy if you have sensitive data that you need to save every time it is changed. This data could be an individual cell or a whole range of cells. So, in this small VBA macro, if the defined range of cells change, Excel is forced to save the workbook.
So, let’sSo, let’s look at an example to work through. I have an Excel workbook with my sales figures. Every day I update the workbook with some newly updated numbers. Once I update any of the cells in the range C2:C4, Excel needs to save the workbook.
How Does The Macro Work?
Now, I do not want the workbook to save automatically when any old cell is updated. I just require cells in the range C2:C4 to trigger a file save. I therefore use the INTERSECT method. Intersect determines if the target cell or cells (those that have changed) intersect with the range, we are targeting to trigger a file save. Ok, let’s get started on writing this simple VBA macro.
Starting The Macro.
So, this macro is particular to a range of cells in a worksheet. Therefore the code is placed in the Worksheet_Change event code window.
Step 1. Open the Visual Basic Editor.
If you have not already opened the VB editor then you will need to do this. Hit ALT+F11 or alternatively select the If you have not already opened the VB editor, you will need to follow the following steps. Hit ALT+F11 or select the Developer Tab | Code Group | Visual Basic. Both methods have the same result. Once you are in the Project window, navigate to your worksheet. Click the plus sign to view all worksheets. Double click on the worksheet you want to trigger the code.
Step 2. The Worksheet_Change Event.
Select worksheet from the left-hand drop-down box. Then, select Change from the right-hand drop-down box. The rest of the VB code needs to be written in between these two lines of code.
Step 3. Using the Else IF in VBA
VBA Else If allows you to analyze a condition and perform an action accordingly. IF condition checks if the supplied condition is TRUE or FALSE, if the condition is TRUE it will return the assigned value of Value if True and return Value IF False if the result is FALSE. So, in this example, the condition tests if the target range of cells that have changed are in the range specified by the Intersect method.
The first part of the code tests if the changed range intersects the specified range of C2:C4. If the result of this test is false, then Excel exits the procedure at this stage. So, if there is no intersection between the target cell and the specified range, the code ends.
If Intersect(Target, Range("C2:C4")) Is Nothing Then
Exit Sub
Step 3. Saving The Workbook.
In Step 3 of the code, if there is an Intersection between the target cells and the specified range, the workbook is saved. The End If code closes the If statement.
Else: ActiveWorkbook.Save
End If
Step 4. Ending The Macro.
Once the workbook saved, the The End Sub code already inserted by Excel at the start of the macro kicks in. The macro ends at this stage.
That is is. Once you update or change any of the cells in the range specified the Excel workbook automatically saves. No worries about losing any work. That is how to save a workbook when a specific cell is changed
Do you want even more Excel and Macro Tips?.
Feel free to bookmark my Formula Friday and Macro Monday pages. I update them every week. All of the blog posts are currated.
