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!

My suggestion is to evaluate the Range("G2:G103") on the target sheet when the user saves and closes the workbook. When you make your check at this point, the user is "committing" all the changes. Also, if you snap a copy of the range (including the last known data) to your ActivityLog sheet, you could compare each of the cells from the target sheet range to the saved range and know exactly what changes were made (then form those changes into a long string and save it as a log entry: Date, User, CellChanges — PeterT
– PeterT, Commented Apr 3, 2015 at 23:18

score 2 · Accepted Answer · 2015-04-04 16:00:04Z

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.

This is very helpful! I think my problem was putting one formula in either the worksheet module of the workbook module but not having both in at the same time...The EnableEvents seems to be firing fine from the Immediate Window, so we shall see.
@user3794203 - My apologies. I should not have left this point unclear. Use one or the other worksheet or workbook routines. Do not use both! While I don't believe any real harm could be done, there is the possibility of them 'walking on top of each other' and that can only cause an unstable environment. Please use one or the other. They can both be there doing different things but they should not both be there trying to do the same thing.

Collectives™ on Stack Overflow

Using VBA in the Workbook_SheetChange Event

1 Answer 1

3 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Linked

Related