How to Trigger VBA Code When a Worksheet is Updated

Automation is not as difficult as you might think. In fact, in some use cases, it is so simple that you will find using excel formulas more complicated in comparison. Now for those who don’t know, VBA stands for Visual Basic for Applications. It's a programming language developed specially to implement automation tasks on Microsoft Office applications and in my experience it works wonders while using especially with Excel.

Now for today, we will see how to automate a task of updating a cell value when any kind of change happens in a cell of an excel worksheet. I will explain from scratch so that it becomes crystal clear for even beginners.

Problem Statement

Let's take a hypothetical situation where we have a list of employees and every day a Sales Target is updated next to the names of each employee. And each employee has been instructed to update the Sales they have done in the “Actual Sales” column.

Create a automated tool which will do below activities when an employee enters the Sales number

1. Enter the difference of Target & Actual Sales in “Delta” column

2. If the difference is greater than 5, then give a pop up message to the user about the difference

3. Capture the Date & Time when employee has entered the sales number in the “Time” column

How to get to the VBA editor in Excel

• Open a workbook & save it as “Macro Enabled Workbook”

• Click on the Developer tab

Steps to follow if you don't see the Developer tab

• Click on “File” and then “Options”
  

• In the following dialogue box, click on “Customize Ribbon” and then enable the checkbox which says “Developer”. Press OK
  

• Now click on the Visual Basic button in the Developer tab. Keyboard shortcut for going directly into the Visual Basic Editor is “Alt + F11”.
  

You will see a window similar to below. This is called the VBA editor & we write all of our codes in this window.

Solution: Triggering VBA Code When a Worksheet is Updated

• Since we want the code to run when any change is done in Sheet1, double click on the Sheet1 object from the Project Window

• Select Worksheet from the Code Window

• Select Change from the adjacent dropdown
  

• Editor will automatically enter a piece of code for you, which represent the block when any kind of change happens in that specific worksheet
  

• We need to enter our code inside the block highlighted in Red. Ignore the block at the bottom since this was selected by default when you clicked on Worksheet dropdown.
  

• Enter following code inside the block

 '1. Declaring a worksheet for easy reference later
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1")


 '2. Code which will trigger when a change happens in Range D4:D12 in Sheet1
    If Not Intersect(Target, Range("D4:D12")) Is Nothing Then
        
 '3. Handles error if user enters a blank value in the cell
         On Error GoTo errHandler
                
 '4. Storing values in variables for future use
        vchg = Target.Row
        vTgt = ws.Range("C" & vchg).Value
        
 '5. Storing values in the Delta & Time column
        ws.Range("E" & vchg).Value = vTgt - Target
        ws.Range("F" & vchg).Value = Now()
        
 '6. Message popup for the user
        If vTgt - Target > 5 Then
            MsgBox "You have not achieved your sales target!", vbInformation
        End If
        
        
    End If

errHandler:

Code Explanation: I have divided the code in multiple sections and commented on them for better understanding. However I will explain each section so that you get a grasp of the entire flow.

• Here we are just declaring our worksheet, so that it's easier to refer the worksheet as WS instead of Sheet1 in later part of the code

• This block is the most important bit because this is where the magic happens. This block identifies there has been any change in the cells in the range D4:D12. And if there is any change then all the code that we write within this block will run

• This is just an error handling code, so that if a user deletes a value from the sales column and leaves it blank, then the code understands the blank value and will not try to run the following code. It will directly jump to the last line which says “errHandler:”

• Here we are storing a few values in variables for easy reference.

Note: I have not declared these variables here because VBA automatically assigns data types to variables based on the value stored in them. But it's always a best practice to declare your variables.

• Now we are inputting values in the columns as per our requirement

• This final block checks if the difference between Target & Sales is greater than 5 or not. If yes then it give a popup message for the user

We have successfully implemented an automated tool which will do certain activities activities when any change happens in the defined range.

Other Options :

Apart from the Worksheet_Change vba event, we have other options as well, which you can choose as per your need. There are 5 other common ways which can be used to trigger a Vba event :

• Worksheet Calculate Event: If your updates involve formulas or calculations that change values in the worksheet, you can use this event to trigger VBA code after calculations are done.

Private Sub Worksheet_Calculate()
    ' Your code here
End Sub

• Worksheet Selection Change Event: This event is triggered when the selection changes within the worksheet. You can use it to run code based on the selected cell or range.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    ' Your code here
End Sub

• Workbook SheetChange Event: This event triggers whenever any cell in any worksheet of the workbook is changed. It's useful for global changes across multiple sheets.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    ' Your code here
End Sub

• Manual Button or Form Control: Create a button or a form control (like a checkbox) on the worksheet and assign a macro to it. Users can click the button or check/uncheck the control to trigger specific VBA code.

Remember, for the first four methods, the VBA code needs to be placed within the specific worksheet's code module. Choose the method that best fits the specific scenario you're dealing with in your Excel project!

That's it for now, stay tuned for the next one!