Excel, VBA, and the Worksheet.Change Event

Excel, VBA, and the Worksheet.Change Event - Do you have users that change a worksheet, and at each change, you are required to check if some specific action is required? Then you can use the Worksheet.Change event provided in VBA. You can find more information about the Worksheet change event at Microsoft's Dev Center. In this article, we will code a short Worksheet.Change script.

Start by giving your sub the name Worksheet_Change. This is important, as the event will not be caught otherwise. You can then start checking where the change happened.

Private Sub Worksheet_Change(ByVal Target As Range)

    MsgBox "You changed cell: " & Target.Address

End Sub

Now we can also start checking if the change occurred in the right cell. Let's say that we only want to check for changes in column B. We add an if check for the range as follows:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Column = 2 Then

        MsgBox "You changed cell: " & Target.Address

    End If

End Sub

Let's say that you also want to change back cell A1's value to 99 for a reason. We can then just add a check if we intersect that target. It is really important that you disable events when you are changing the value of the cell. Remember, we are running the Sub at worksheet changes so this check will run every time you change something.

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Column = 2 Then

        MsgBox "You changed cell: " & Target.Address

    End If

    If Intersect(Target, Range("A1:A1")) Is Nothing Then

        ' Do nothing

    Else

        Application.EnableEvents = False

        Target.Value = 99

        Application.EnableEvents = True

    End If

End Sub

That's it, folks!