Worksheet Change Event
Automatically Run Excel Macros When a Cell Changes
VBA Change to a Single Cell
In Excel a Worksheet Change Event is a trigger for a macro when a cell or group of cells change. I will start out by showing how a change to a single cell can trigger an action. The following will colour cell B2 Red whenever the cell changes. The following uses the(ByVal Target As Range) line which uses the Variable named Target. The Target is the Range which will trigger an action. You assign the Range within the code itself.
Before you fill your boots with the following it is worth mentioning that when you employ the use of the VBA change events you lose the ability to undo in Excel. Normally Excel keeps a record of a number of actions.
The VBA code to perform this action needs to go in the sheet object you want to perform the event. If you wanted to put the code in Sheet1 then you would double click on the sheet you wish to run the code from.
The following is an example of Excel VBA coding you could put in Sheet1 or any of the other sheet objects.
In the example above you need to keep the $ (absolute sign) or the code will not work. So when referencing a single cell the range reference needs to be absolute.
The following VBA performs the same action as the above example. It is a little more flexible if you wish to add to the range. Once Inside the Worksheet Change Event, if the Target falls within the defined Range and the cell contents change, it will trigger an action inside VBA.
Private Sub Worksheet_Change(ByVal Target As Range) 'Excel VBA with more cells in the range.
Occasionally one of the things you may wish to do with the cell that is changing is delete, copy, cut or some other action which triggers a circular loop. For example, if you wanted to move a line to another sheet which met a condition, when the condition was met you would trigger the Change Event and when you deleted the row you would start another change event. This second change event would cause a debug error. To get around this you can turn Events off at the start of the procedure and turn them back on at the end of the procedure.
The line of code is;
and the following is an example of how it might be used.
If Target="Closed" Then
The VBA macro will copy the entire row from one sheet to another and delete the row which was just copied. The example is shown in the file below.
VBA Worksheet Change Event Multiple Cells
When we want to perform an action when more than one cell is changed we can use the following VBA code to change a larger range.
Private Sub Worksheet_Change(ByVal Target As Range)
VBA Double Click Event
A double click event in Excel VBA is self explanatory. It will occur on double click of a cell in the Target range. So if you have a range between C13 and O26 where you want to perform an action on Double click, the following should help.
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
VBA Before Save Event
This event is triggered as the name suggests before each Save. So as the save Excel file icon is clicked the code which is associated with this event will trigger.
The before Save event needs to go into the ThisWorkbook Object in order for it to run.
The following Excel VBA macro will put the word False in Cell A1 before the file is saved.
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)