Hello Excellers, welcome to another blog post in my #MacroMonday series. 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. When you’re working with Excel, there may be times when you want to save the workbook automatically whenever a specific cell or cells change.! 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. In this blog post, we will show you how to do this. Stay tuned for some Macro Monday fun.
If you want to read all of my Macro Monday blog posts then they are available with the link below.
How To Excel At Excel – Macro Mondays Blog Posts.
What Does The Macro Do?
Once a cell or range of cells you specify is changed by a user, this macro forces saving of the Excel workbook.
How Does It Work?
This macro makes use of the Intersect method. We do not want the workbook to save just at any time, and 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?. Get coding!
Starting The Excel Macro.
First, you will need to open the Visual Basic Editor. There are two ways to do this. Either by hitting ALT +F11 or selecting the Developer Tab | Code Group | Visual Basic. Both methods have the same result. As this macro refers to the cell changes in the current workbook, it makes sense to store this code in the same workbook.
So, this macro needs to go into the Worksheet_Change event code window. This location allows the code to run anytime you make changes to the sheet.
In your Project window, find the name of the Project/Workbook and then navigate to the worksheet that you want to trigger the code. In my example, it is MM13. Next, select the Change event from the drop-down list available.
Writing The Save Excel On Change Code.
We can now start writing our code. You can see it in detail below.
Step 1. Use The VBA If Statement.
The first line of code uses the IF statement. This statement differed slightly from the Excel IF StatmentThe IF statement will perform only the first half of the statement, checking if the condition evaluates to TRUE or FALSE. In addition THEN statement needs to be entered in VBA IF statement for remaining operation or task to perform.
So, to recap the syntax of the VBA IF statement. There are three parameters.
- A condition or parameter to test.
- An operation or task to perform if the condition is TRUE.
- An operation or task to perform if the condition is FALSE.
In this example, Excel determines if the changed range intersects the specified range (C5:C6). Intersect method determines if the target cell (the cell that changed) intersects with the range, we have specified to be the trigger range.
Step 2. If The Condition Is True Exit The The Procedure.
In this next line of code, if there is no intersection with the specified range then Excel exits the procedure. As the cells changed have not met the condition, the file is not saved at this point.
Step 3. If There Is An Intersection Then Save The Excel Worksbook.
In this almost final code step, Excel saves the workbook if the changed cells interest the range of cells C5:C6. Then, the Save method triggers to save the active workbook. This process overwrites the previous copy of the Excel workbook. Once this line of code ends, the procedure ends with the End Sub line of code. This line of code is placed in the code window once the Worksheet_Change event was selected.
Saving an Excel workbook when a specific cell or cells change is possible with the use of macros. If users are entering critical information into the Excel file, this method avoid manual saving.
Do You Want To Copy The Code For free?
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
Exit Sub
Else
ActiveWorkbook.Save
End If
End Sub
Other Related Blog Posts To Read.
Even More Excel Tips.
Recommended Excel Courses.
