Excel Worksheet_Change event not working

Question

I am trying to write a macro in which any column changes should automatically save the worksheet.

My Excel worksheet expands to G25 .

I tried this but did not work:

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

I saved it under ThisWorkBook .

Any help is appreciated.

Answer 1

ThisWorkbook calls this handler Workbook_SheetChange and takes two arguments: Sh (of type Object ) and Target (a Range ). This way your code will not work.
If you place your code on the desired sheet ( Sheet1 , for example), and not on ThisWorkbook , put End If and change the range to “A1: G25” (representing the square from column 1 of row A to row 25 of column 25), it should work. It is happened:

 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, thisWorkbook 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

Another common reason an event does not work is because EnableEvents is set to False.

I would call your problem as follows:

• Usually you need to work with our range of interests. Therefore, creating the rng1 variable below serves both as an early exit point and as a range object for operation. On a sheet, the Target.Worksheet.Range("A1:G25") event Target.Worksheet.Range("A1:G25") will work, but it is lengthy for its actual use.

• If you have a range for control, then making any further changes to the sheet will trigger a change event, etc., which may cause Excel to crash. Therefore, it is better to disable further events, run your code, and then re-enable events when exiting

   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