excel - Using VBA in the Workbook_SheetChange Event

Question

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.

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

Thanks!

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

Thank you!

Answer 1

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)

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.

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

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

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.