在Workbook_SheetChange事件中使用VBA [英] Using VBA in the Workbook_SheetChange Event

问题描述

我正在尝试创建一个日志，以记录特定工作表(名为"FORMULAS")中的特定范围(G2:G103)对其进行了任何更改.这本工作簿每天被很多人多次查看，这对我记录下有关范围的更改时间有帮助，这对我很有帮助，我可以在后台进行跟踪.我希望更改日志包含在另一个工作表(名为"ActivityLog")中，该工作表从E列开始，并使用用户名和now函数.

I am trying to create a log of when a specific range (G2:G103) within a specific worksheet (named "FORMULAS") has any changes made to it. This is a workbook that is viewed by many people multiple times a day, and it would be helpful for me to have record of when changes were made to the range in question that I can keep track of behind the scenes. I would like the log of changes to be contained within another worksheet (named "ActivityLog") starting in column E with the username and now function.

到目前为止，我编写的代码没有返回错误，但是什么也不做.我同时尝试了一个worksheet_change事件和一个workbook_sheetchange事件，并遇到了相同的问题:什么也没做.对我缺少的内容以及是否应将代码放在"FORMULAS"模块或"ThisWorkbook"模块中有任何想法吗?

So far the code that I have written does not return an error, but does not do anything at all. I tried both a worksheet_change event and a workbook_sheetchange event and kept running into the same issue: not doing anything. Any thoughts on what I am missing and on whether or not I should place the code within the "FORMULAS" module or within the "ThisWorkbook" module?

Application.EnableEvents = False
If Intersect(Target, Range("G2:G103")) Then
With Worksheets("ActivityLog")
Sheets("ActivityLog").Range("E" & Rows.Count).End(xlUp).Offset(1, 0).Value =    Environ("username")
Sheets("ActivityLog").Range("E" & Rows.Count).End(xlUp).Offset(0, 1).Value = Format(Now, "MM/dd/yyyy h:mm:ss_ AM/PM")
End With
End If
Application.EnableEvents = True

谢谢！

以上感谢@Jeeped现在已得到答复.但是，我决定采用这种方式遇到另一个问题.由于所讨论的范围相当大，并且每当进行更改时宏都会向ActivityLog工作表发送重复的报告(因为该单元格已激活，并且值已更改，我想这就是它翻倍的原因)，我正在尝试看看我是否真的只想看看是否发生了更改(不一定发生了多少个更改)，是否可以缓解大量的活动日志.我有一个公式单元格，可以跟踪总更改的值，所以我认为这可能起作用，并且宏被触发一次之后又不会起作用...有什么想法吗? (这是工作表中我正在查看的单元格所在的工作表中的专用模块.)

The above has been answered now thanks to @Jeeped. I have run into another issue with this way that I decided to go, however. As the range in question in rather large and the macro sends a duplicate report to the ActivityLog sheet anytime a change is made (because the cell is activated, and the value is changed, I am guessing that is why it's doubled), I am trying to see if I can mitigate a huge activity log if I really only want to see if a change happened or not (and not necessarily how many changes took place). I have a formula cell that keeps track of the value of total changes, so I thought that this might work, and the macro fired once and then would not work again...any thoughts? (This is a private module in the sheet where the cell I am watching with the formula resides.)

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("E14")) Is Nothing Then
Call Worksheet_Calculate
End If
Application.EnableEvents = True
End Sub

Sub Worksheet_Calculate()
Static oldval
If Range("E14").Value <> oldval Then
oldval = Range("E14").Value
Application.EnableEvents = False
With Worksheets("ActivityLog")
.Range("E" & Rows.Count).End(xlUp).Offset(1, 0).Value = Environ("username")
.Range("E" & Rows.Count).End(xlUp).Offset(0, 1).Value = Format(Now, "MM/dd/yyyy h:mm:ss_ AM/PM")
    End With

End If
End Sub

谢谢！

推荐答案

我对您的代码的原始评估有点草率.检查目标是否在G2:G103中应该检查If Not Intersect(Target, Range("G2:G103")) Is Nothing，而不仅仅是Intersect.除此之外，该代码看起来还不错. (With ... End With是多余的，但这不应阻止它运行)

My original evaluation of your code was a bit hasty. The check to see if target is within G2:G103 should check If Not Intersect(Target, Range("G2:G103")) Is Nothing, not just the Intersect. Other than that, that code looks fine. (the With ... End With is redundant but that shouldn't stop it from running)

在工作表代码表中使用Worksheet_Change事件宏，或在ThisWorkbook工作簿代码表中使用Workbook_SheetChange.他们俩都可以在那里做不同的事情，但是他们俩都不应该都在试图做同样的事情.从下面提供的两个中选择一个或另一个.

Use a Worksheet_Change event macro in the worksheet code sheet or the Workbook_SheetChange in the ThisWorkbook workbook code sheets. They can both be there doing different things but they should not both be there trying to do the same thing. Pick one or the other from the two offered below.

在FORMULAS工作表代码表中:

In the FORMULAS worksheet code sheet:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("G2:G103")) Is Nothing Then
        Application.EnableEvents = False
        With Worksheets("ActivityLog")
            .Range("E" & Rows.Count).End(xlUp).Offset(1, 0).Value = Environ("username")
            .Range("E" & Rows.Count).End(xlUp).Offset(0, 1).Value = Format(Now, "MM/dd/yyyy h:mm:ss_ AM/PM")
        End With
    End If
    Application.EnableEvents = True
End Sub

在ThisWorkBook工作簿代码表中:

In the ThisWorkBook workbook code sheet:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    If Sh.Name = "FORMULAS" Then
        If Not Intersect(Target, Sh.Range("G2:G103")) Is Nothing Then
            Application.EnableEvents = False
            With Worksheets("ActivityLog")
                .Range("E" & Rows.Count).End(xlUp).Offset(1, 0).Value = Environ("username")
                .Range("E" & Rows.Count).End(xlUp).Offset(0, 1).Value = Format(Now, "MM/dd/yyyy h:mm:ss_ AM/PM")
            End With
        End If
    End If
    Application.EnableEvents = True
End Sub

但是，.EnableEventws是否停留在False的问题仍然有效.您是否进行过先前的尝试并中途崩溃?转到VBE的立即窗口(Ctrl + G)并粘贴Application.EnableEvents = True，然后按Enter.如果在之前的代码中途发生崩溃，则EnableEvents可能会停留在False上.

However, the question of whether .EnableEventws is stuck at False is still valid. Did you run previous attempts and crash halfway through? Go to the VBE's Immediate Window (Ctrl+G) and paste in Application.EnableEvents = True then hit Enter. EnableEvents may be stuck at False if a crash occurred halfway through the previous code.

这篇关于在Workbook_SheetChange事件中使用VBA的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！