VBA Excel Worksheet.Change Event

This Excel VBA tutorial explains how to use Worksheet.Change Event.

You may also want to read:

Excel VBA get value before Worksheet_Change event

VBA Excel Worksheet_Change Event

Excel predefines some popular actions that you would do on different Objects (worksheet, workbook, button, etc), those actions are called Event. For example, activating a worksheet is an Event, closing a workbook is an Event, clicking on a button is an event. Each Object has its own list of Events, Workbook has a list of Events (e.g. close workbook, open workbook), worksheet has a list of Events (e.g. activate worksheet, edit a Cell).

If you perform an Event, say, closing a workbook, your desired code can be triggered. For example, you may want to save a workbook automatically when you close a workbook, or you may want a welcome message box to pop up when a workbook is opened. Event is a Sub Procedure (begin with Private Sub and end with End Sub) and is generated automatically (see in the below section) with a specific name, you can call a Sub Procedure or write your own code within the Event code.

Excel Worksheet_Change Event is an Event triggered when a you leave a Cell from edit mode (even no value is changed). For example, you double click on Cell A1 to enter edit mode, the event is triggered as you press “Enter” or click on any other Cell to exit the edit mode. Excel Worksheet_Change Event is not about value change of a Cell (of course the Event will trigger if you change a value), don’t be misled by the name.

If you want to know how to capture the initial value before change in order to compare the old and new value, read the below article

Excel VBA get value before Worksheet_Change event

How to insert Excel Worksheet_Change Event

Like all other worksheet events, you have to define the event and your desired actions within a specific worksheet where you want to Macro to trigger, each worksheet can have its own independent events.

1) Press Alt+F11 to enter into Visual Basic Editor

2) In the Project Explorer Window on the left, double click on the target worksheet

3) On top of the coding area, select “Worksheet” in the drop down box on the left, and then select “Change”.

4) Now you should be able to see two lines of code as below. Insert your action code between the two lines.

Example of Excel Worksheet_Change Event

For example, I want to prompt a message box if column A value >100.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 1 And Target.Value > 100 Then
        MsgBox ("Column A value >100")
    End If
End Sub

In the above code, “Target” is the Range you make a change. Target.Column = 1 bounds the checking to column A.

Trigger Macro when Value Changes

Below is a solution I copied and pasted from Microsoft Community that was answered by me.

Question

How can you instruct a macro to run when a certain cell changes?

For example, as soon as text in cell A1 changes, a macro is triggered.

Answer

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target, Range("A1:B10")) Is Nothing Then
     Dim OldValue As Variant
     Application.EnableEvents = False
     Application.Undo
     OldValue = Target.Value
     Application.Undo
     Application.EnableEvents = True
     If OldValue <> Target.Value Then
        'Your Macro
     End If
  End If
 End Sub

Dim oldValue As Variant
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Not Intersect(Target, Range("A1:B10")) Is Nothing Then
        oldValue = Target.Value
    End If
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("A1:B10")) Is Nothing Then
        If Target.Value <> oldValue Then
            'Do something
        End If
    End If
End Sub

Outbound links

http://msdn.microsoft.com/en-us/library/office/ff839775%28v=office.15%29.aspx#AboutContributor