change log with VBA

[Mattlake]

Hi

I hope you can help me with a spreadsheet.

I have a spreadsheet with 3 tabs (AY21-22, AY22-23 and log[hidden]) that I need to track changes on.

I have found

Private Sub workbook_beforesave(ByVal SaveAsUI As Boolean, cancel As Boolean)

Dim wbLog As Worksheet
Dim iLastRow As Integer

Set wbLog = Sheets("log")
iLastRow = wbLog.Cells(wbLog.Rows.Count, "A").End(xlUp).Row + 1

wbLog.Cells(iLastRow, 1).Value = Environ("username")
wbLog.Cells(iLastRow, 2).Value = Now

End Sub

which works to log the user and when they access.

I want to also log which cells are altered even if it is just which cells are edited. Is this possible?

I have tried googling different ways to final a solution but unable to find anything (not even on youtube)

So it may not even be possible but if a VBA master can tell me that it isnt possible i would be soo happy and relieved.

Regards

Matthew

 

[Jeffrey Mahoney]

Please try this. I assumed you wanted the Log sheet to record these changes. I added fields onto the LOG sheet below. The VBA code below needs to be posted into the "ThisWorkbook" module of the project.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
  Dim Ar As Range
  Dim LogSht As Worksheet
  Dim LogUserHdr As Range
  Dim Cel As Range
  Dim Astr As String
  

  Select Case Sh.Name
    Case "AY21-22", "AY22-23"
      Set LogSht = Log
      Set LogUserHdr = LogSht.Range("LogUserHdr")
      Set Cel = LogSht.Cells(LogSht.Rows.Count, LogUserHdr.Column).End(xlUp).Offset(1, 0)
      Cel.Value = Application.UserName                                                        'Username
      Cel.Offset(0, 2).Value = Now()                                                          'Date / Time
      For Each Ar In Target.Areas
        If Len(Astr) > 0 Then
          Astr = Astr & ", " & Ar.Address
        Else
          Astr = Ar.Address
        End If
      Next Ar
      Cel.Offset(0, 1).Value = Astr
  End Select
  

End Sub

Book2
	A	B	C	D
1	User	Sheet	Range	Date/Time
2	Jeff	AY22-23	$G$7	5/19/2023 7:43
3	Jeff	AY22-23	$C$11	5/19/2023 7:44
4	Jeff	AY22-23	$C$11:$M$28	5/19/2023 7:44
5	Jeff	AY22-23	$C$15:$D$17, $H$20:$H$22, $K$14:$K$16	5/19/2023 7:44
6	Jeff	AY21-22	$C$14:$D$14, $E$10:$F$10, $F$16:$G$16	5/19/2023 7:44
Log

 

[Jeffrey Mahoney]

Oh, the cell A1 on the log sheet needs to have a named range called "LogUserHdr". Darn it, the Sheet named Log should have it's code named changed to Log. Just click on the sheet in VBA, in the properties window below, change the sheet code name.

 

[Jeffrey Mahoney]

Now, if you workbook is large with a ton of formulas, this will slow things down a little more. This only tells you if cells were changed. It won't tell you if they got deleted, altered, or added.

You could record the cell contents after the change, but that would add way more content to your log.

And if you wanted to detect if the changes were malicious, you're going to need to add the library for user mind reading: MindReading101NonExistent.DLL

 

[Jeffrey Mahoney]

Please accept this change in the code. I made some corrections

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
  Dim Ar As Range
  Dim LogSht As Worksheet
  Dim LogUserHdr As Range
  Dim Cel As Range
  Dim Astr As String
  

  Select Case Sh.Name
    Case "AY21-22", "AY22-23"
      Set LogSht = ThisWorkbook.Worksheets("Log")
      Set LogUserHdr = LogSht.Range("LogUserHdr")
      Set Cel = LogSht.Cells(LogSht.Rows.Count, LogUserHdr.Column).End(xlUp).Offset(1, 0)
      Cel.Value = Application.UserName                                                        'Username
      Cel.Offset(0, 1).Value = Sh.Name                                                        'sheet name
      Cel.Offset(0, 3).Value = Now()                                                          'Date / Time
      For Each Ar In Target.Areas
        If Len(Astr) > 0 Then
          Astr = Astr & ", " & Ar.Address
        Else
          Astr = Ar.Address
        End If
      Next Ar
      Cel.Offset(0, 2).Value = Astr
  End Select
  

End Sub

 

[Mattlake]

Thank you for this. i will never understand how you all keep this knowledge.

so i have pasted the new script into the this workbook object (or do I need to put it into a new module and set it to run automatically how ever I do this)

I have also changed the log sheet to Log

but when i complete a change on the AY22-23 sheet, it comes up with a debug on line

so i have likely mucked up just pasting

Regards

Matthew

 

[Jeffrey Mahoney]

In post #3 I talked about the named range that you need to create. It's the header of the log table

 

[Mattlake]

Sorry i feel I am being so dumb with this.

on the sheet called log I just put LogUserHdr into A1? or create the column titles as user , date etc?

 

[Jeffrey Mahoney]

This site is here to learn. Ask as many questions as you need.
Put your cursor on Cell A1, then Using the Excel menu: Formulas > Name Manager > New > type in the name given and press enter

 

[Mattlake]

you are brilliant

thank you so much for this