Excel Worksheet_Change Event not working

Question

I am trying to write a macro where changing any column should automatically save worksheet.

My Excel sheet expands till G25.

I tried this but its not working:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Target.Worksheet.Range("G25")) Is Nothing Then
        ActiveWorkbook.Save
End Sub

I have save it under ThisWorkBook.

Any help is appreciated.

Answer 1

Under ThisWorkbook that handler is called Workbook_SheetChange and it accepts two arguments, Sh (of type Object) and Target (a Range). So, your code won't work there.
If you put your code in the sheet you want (Sheet1, for instance) instead of in the ThisWorkbook, put the End If and change the range to "A1:G25" (representing a square from row 1 column A to row 25 column 25), it should work. This did:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Target.Worksheet.Range("A1:G25")) Is Nothing Then
        MsgBox "changed"
    End If
End Sub

For completeness, under ThisWorkbook, this will work:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    If Not Intersect(Target, Target.Worksheet.Range("A1:G25")) Is Nothing Then
        MsgBox "changed"
    End If
End Sub

Answer 2

The other common reason why an Event doesn't work is that EnableEvents has been set to False

I would code your problem like this as

• typically you need to work with the range of interest that is being tested. So creating a variable rng1 below serves both as an early exit point, and a range object to work with. On a sheet event Target.Worksheet.Range("A1:G25") will work but it is lengthy for it's actual use

• If you do have a range to manipulate then making any further changes to the sheet will trigger a recalling of the Change event and so on, which can cause Excel to crash. So it is best to disable further Events, run your code, then re-enable Events on exit

  Private Sub Worksheet_Change(ByVal Target As Range)
  Dim rng1 As Range
  Set rng1 = Intersect(Target, Range("A1:G25"))
  If rng1 Is Nothing Then Exit Sub
  Application.EnableEvents = False
  'work with rng1
  Application.EnableEvents = True
  End Sub