Macro for tracking changes on multiple worksheets

[OSUBuckeyes]

This is my first question on this website but I've been using this site for a while now teaching myself basic VBA skills...thank you all so much!

Now to my question...

I'm looking to have a macro that will track any change on any worksheet. I've found code that does this but it only references the cell (A1 or B25) the change occurred. We have several tabs so we need to know which sheet and cell changed.

Below is the code that I am using, found on another website. What do I need to add to this to see what worksheet the change occurred? If there is a more efficient/cleaner way than below, I am open to that suggestion as well.

*Disclaimer, this is a shared file and the method to view the history of the changes via the built in function is messy, in my opinion. I'd prefer to just have a log using this macro.

Thank you in advance!


Dim vOldVal 'Must be at top of module



Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

Dim bBold As Boolean



If Target.Cells.Count > 1 Then Exit Sub

On Error Resume Next



With Application

.ScreenUpdating = False

.EnableEvents = False

End With



If IsEmpty(vOldVal) Then vOldVal = "Empty Cell"

bBold = Target.HasFormula

With Sheets("Change Log")



If .Range("A1") = vbNullString Then

.Range("A1:E1") = Array("CELL CHANGED", "OLD VALUE", _
"NEW VALUE", "TIME OF CHANGE", "DATE OF CHANGE")

End If





With .Cells(.Rows.Count, 1).End(xlUp)(2, 1)

.Value = Target.Address

.Offset(0, 1) = vOldVal

With .Offset(0, 2)

If bBold = True Then

.ClearComments

.AddComment.Text Text:=Chr(10) & "" & Chr(10) & "Bold values are the results of formulas"

End If

.Value = Target

.Font.Bold = bBold

End With



.Offset(0, 3) = Time

.Offset(0, 4) = Date

End With

.Cells.Columns.AutoFit




End With

vOldVal = vbNullString



With Application

.ScreenUpdating = True

.EnableEvents = True

End With



On Error GoTo 0



End Sub



Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)

vOldVal = Target

End Sub

 

[jmacleary]

Hi there and welcome.

You can use: Target.Worksheet.Name to get the sheet name, so you could make these small changes:

[I].Range("A1:F1") = Array("CELL CHANGED", "OLD VALUE", _[/I]
[I]                        "NEW VALUE", "TIME OF CHANGE", "DATE OF CHANGE","WORKSHEET CHANGED")[/I]

and add a new line after '.Offset(0, 4) = Date' such as: '.Offset(0, 5) =Target.Worksheet.Name'


Regards
John

 

[OSUBuckeyes]

Thank you so much jmacleary! This worked perfectly!

 

[jmacleary]

You're very welcome. Could you give me an upvote on the likes and thanks buttons please?

 

[OSUBuckeyes]

jmacleary: This is a workbook that needs to be a shared file. When I make this a shared file by tracking changes, the macro no longer records any changes. Could you please shed some light on this for me?

 

[jmacleary]

I'm sorry, but no. I created a simple workbook and inserted your code. It records changes whether its shared or not, and whether i'm tracking changes or not. Just a thought though, if your macro is recording the changes in your log, do you need to track changes as well? Try just having it as a shared book.

 

[OSUBuckeyes]

Hmm. OK. I'm not sure why mine isn't recording changes when the file is shared. I could but I would lose the record of changes because there are times when the file becomes un-shared for maintenance purposes (adding new tabs for projects and updating code for summary data). Any other thoughts would be appreciated.