VBA Worksheet Change Event

7
votes

I am trying to use the Worksheet Change Event in Excel VBA, but it doesn't seem to work.

From what I gather, it is enough to just define the handling function "Worksheet_Change" as I have done here:

Private Sub Worksheet_Change(ByVal Target As Range)
    Range("J1").Select
    If Target.Address = "$J$1" And ActiveCell.Value = 1 Then
        Range("B1").Select
        Dim c As Integer
        c = ActiveCell.Value
        c = c + 1
       ActiveCell.Value = c
    End If
End Sub

The problem is that I am not sure exactly where I am supposed to define it. I have just put it in "module1" which was automatically generated when I made my first macro. Is this correct? I am quite new to VBA, so I don't know much about it yet.

1
excelvba

1 Answers

11
votes

You need to put it in the sheet that it applies to. In other words, if you want to capture change events on Sheet1, in the VBA editor, you need to put it in VBAProject (Book_Name) > Microsoft Excel Objects > Sheet1.

enter image description here