17.06.2018

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!

Featured image from Wikimedia Commons, Picture address here

One comment on “Excel, VBA, and the Worksheet.Change Event”

Leave a Reply

Your email address will not be published.

linkedin facebook pinterest youtube rss twitter instagram facebook-blank rss-blank linkedin-blank pinterest youtube twitter instagram