CODEWITHSUNDEEP
vbaeventscell
user2644683
user2644683

Reputation: 1

Cell Events are not triggerred

Need a help on Cell Event Macro, I am testing Cell Event Macros and due to some reason its not triggering. Request your help to look into it

Code

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("J8").Value = "No" Then
MsgBox "Hi"
Else
Exit Sub
End If
End Sub

Code

Not Sure, where is the issue here, my expectation is to show a message box When I typed "No". However its not working

Upvotes: 0

Views: 36

Answers (1)

Variatus
Variatus

Reputation: 14373

Below is the basic code you need and it must be installed in the code module of the sheet on which you want the action because the change event won't be noticed anywhere else.

Private Sub Worksheet_Change(ByVal Target As Range)

    Const TriggerCell   As String = "J8"
    
    If Target.Address(0, 0) = TriggerCell Then
        If Target.Value = "No" Then
            MsgBox "Hi"
        End If
    End If
End Sub

This code is very crude, just for demonstrating the principle which is as follows.

  1. The event generates a range called Target. That's the cell that was changed. If you use copy/paste, many cells could be changed at the same time. Then Target would be a bigger range.
  2. You must determine if the changed cell is of interest to you. In the above code this is done by specifying a TriggerCell. You might specify an entire column (but not in the above setup which can handle only one cell).
  3. Since the Target provided by the event is the TriggerCell you have specified you can compare its value with your expectation and take action depending upon the result of that comparison.

Note that not Else or Exit Sub are required before the end of the sub.

Upvotes: 2

Related Questions