vba Excel Worksheet_Change 事件不起作用

Excel Worksheet_Change Event not working

提问by user234194

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

我正在尝试编写一个宏，其中更改任何列都应自动保存工作表。

My Excel sheet expands till G25.

我的 Excel 工作表扩展到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.

我把它保存在ThisWorkBook.

Any help is appreciated.

任何帮助表示赞赏。

回答by ssarabando

Under ThisWorkbookthat handler is called Workbook_SheetChangeand 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 Ifand change the range to "A1:G25"(representing a square from row 1 column A to row 25 column 25), it should work. This did:

在ThisWorkbook 下，该处理程序被调用Workbook_SheetChange，它接受两个参数，Sh（类型Object）和目标（a Range）。所以，你的代码在那里不起作用。
如果您将代码放在所需的工作表中（例如Sheet1）而不是在ThisWorkbook 中，请将End If范围更改为“A1:G25”（表示从第 1 行 A 列到第 25 行第 25 列的正方形），它应该工作。这做到了：

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:

为了完整起见，在ThisWorkbook下，这将起作用：

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

回答by brettdj

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

事件不起作用的另一个常见原因是 EnableEvents 已设置为 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 rng1below 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
  

• 通常，您需要处理正在测试的感兴趣的范围。因此，在rng1下面创建一个变量既可以作为早期退出点，也可以作为要使用的范围对象。在Target.Worksheet.Range("A1:G25")工作表上事件将起作用，但它的实际使用时间很长

• 如果您确实有要操作的范围，则对工作表进行任何进一步更改将触发 Change 事件的调用等，这可能会导致 Excel 崩溃。所以最好禁用更多的事件，运行你的代码，然后在退出时重新启用事件

  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