Worksheet_Change Never FIres

Question

I am using VBA to alter Excel and have the following simple event handler inside the Sheet1:

Private Sub Worksheet_Change(ByVal Target As Range)
    MsgBox "HI"
End Sub

This never fires though, ever. I have tested in the Immediate Window:

?Application.EnableEvents

which ultimately returns True, so it should fire. Does anyone know why this doesn't want to fire at all?

Answer 1

Do you have more than one sheet? Be aware that the sheet's CodeName property and the Name property (the name on the tab in Excel) aren't always the same. For instance, if the Project Explorer lists

Sheet2 (Sheet1)

Then Sheet1 is the Name and Sheet2 is the CodeName.

The easiest way to see if you have put the code in the wrong module is to right click on the sheet's tab and choose View Code. That will bring up the CodePane for that sheet and that's where your code should be.

Answer 2

This question was posted a long time ago, but I recently had this same problem. If there are any other vba newbies out there struggling with this, try saving your workbook and re-opening it. After I did that, the Worksheet_Change Sub in my sheet fired as I expected.

Perhaps there is a control to compile worksheet subs that I was missing???

Answer 3

Usually used with Target. Here's an example if you highlight M4 and change the value see what happens:

Private Sub Worksheet_Change(ByVal Target As Range)
 If Target.Address = "$M$4" Then
    MsgBox ("HI")
 End If
End Sub

HERE is quite a nice posting about this event

Adapting code from that post you can then do things like this

Private Sub Worksheet_Change(ByVal Target As Range) 
    If Intersect(Target, Me.Range("$A$1:$V$100")) Is Nothing Then Exit Sub 
    If Target.Address = "$A$1" Then 
        MsgBox ("A1")
    ElseIf Target.Address = "$A$2" Then 
        MsgBox ("A2")
    End If 
End Sub 

Ok - see comments below + I've just tested and your code should actually work. Try this? Does this also not work?

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Me.Cells) Then
        MsgBox ("A1")
    End If
End Sub