Worksheet Change Event - Excel Dashboards VBA

• Home
• Dashboards
• Excel Tips
  • Tips & Tricks
  • Charts
  • Modelling
  • Infographics
  • VBA
• Shop Dashboards
• PowerPivot
  • Power Pivot a User Guide
• Courses
  • Excel Dashboard Course
  • Advanced Dashboard Course
  • Financial Modelling Course
  • Excel VBA Course
• Blog
• About

Menu

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.

The following YouTube video takes you the cell change event, both a single cell and multiple cells. The following Excel file goes with the video.

Change Cell.xlsm

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.

"$B$2”

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.

Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) 'Excel VBA with more cells in the range. If Not Intersect(Target, Range("B2")) Is Nothing Then Target.EntireRow.Interior.ColorIndex=15 End If End Sub

Disable Events

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;

Application.EnableEvents=False

and the following is an example of how it might be used.

Private Sub Worksheet_Change(ByVal Target As Range) 'Excel VBA change event test for close. If Not Intersect(Target, Range("A2", Range("A" & Rows.Count).End(xlUp))) Is Nothing Then Application.EnableEvents=False If Target="Closed" Then Target.EntireRow.Copy Sheet2.Range("A1").End(xlDown)(2) Target.EntireRow.Delete End If End If Application.EnableEvents=True End Sub

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. It focuses on shifting the range within the Target. The following is an example of a change event where if the cells from A2:A10 change the procedure will trigger an action.

Option Explicit 'Excel worksheet change event Range A1 to A10 Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("A2:A10")) Is Nothing Then Target.EntireRow.Interior.ColorIndex=15 End If End Sub

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.

'Excel worksheet double click change event Range C13 to O26 Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) If Not Intersect(Target, Range("C13:O26")) Is Nothing Then Target.Value=ActiveCell.Offset(19, 0).Value End If End Sub

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.

Option Explicit Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Sheet1.Cells(1, 1)=False End Sub