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
[…] the last post about the Worksheet.Change event, we covered how to catch a change event. We showed a MsgBox and […]