Recording Plugin?

[Variety_Bear]

Hi All,

We use an Excel at work that's connected to SQL using 'Data From' function. Are there any plugins out there that can record when a user works on the excel and all the changes they have made? I know Excel had tracking which I used years ago, but it is legacy now and I don't think it can record everything in this Excel with live data from SQL.

Is there anything out there, or any way to record all user changes to this work book?

We have a few users using this and errors keep happening, it would be a great tool to be able to retrain, or to be able to look at in a log what user did what.

Thank you,

 

[Logit]

.
Paste the following in the ThisWorkbook module of the file :

Option Explicit

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
If ActiveSheet.Name = "Pricing" 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("Tracker")
            
                If .Range("A1") = vbNullString Then
                    .Range("A1:H1") = Array("Cell Changed", "Old Value", _
                        "New Value", "Old Formula", "New Formula", "Time of Change", "Date of Change", "User")
                End If

            With .Cells(.Rows.Count, 1).End(xlUp)(2, 1)
                  .Value = ActiveSheet.Name & " : " & Target.Address
                  .Offset(0, 1) = vOldVal
            With .Offset(0, 2)
              If bBold = True Then
                .ClearComments
                .AddComment.Text Text:= _
                     "OzGrid.com:" & 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
                .Offset(0, 5) = Application.UserName
            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

Here is an example of what it tracks :

Cell Changed	Old Value	New Value	Time of Change	Date of Change	User
Testing : $A$1	Empty Cell	A1	01:06:08 PM​	4/15/2019​	My
Testing : $B$8	Empty Cell	911​	11:15:02 AM​	5/23/2020​	My
Testing : $B$8	911​	633​	11:15:59 AM​	5/23/2020​	My

 

[Variety_Bear]

.
Paste the following in the ThisWorkbook module of the file :

Option Explicit

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
If ActiveSheet.Name = "Pricing" 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("Tracker")
           
                If .Range("A1") = vbNullString Then
                    .Range("A1:H1") = Array("Cell Changed", "Old Value", _
                        "New Value", "Old Formula", "New Formula", "Time of Change", "Date of Change", "User")
                End If

            With .Cells(.Rows.Count, 1).End(xlUp)(2, 1)
                  .Value = ActiveSheet.Name & " : " & Target.Address
                  .Offset(0, 1) = vOldVal
            With .Offset(0, 2)
              If bBold = True Then
                .ClearComments
                .AddComment.Text Text:= _
                     "OzGrid.com:" & 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
                .Offset(0, 5) = Application.UserName
            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

Here is an example of what it tracks :

Cell Changed	Old Value	New Value	Time of Change	Date of Change	User
Testing : $A$1	Empty Cell	A1	01:06:08 PM​	4/15/2019​	My
Testing : $B$8	Empty Cell	911​	11:15:02 AM​	5/23/2020​	My
Testing : $B$8	911​	633​	11:15:59 AM​	5/23/2020​	My

Hi Logit,

Thank you so much for providing me with that. Do I need to amend the VBA for it to work on my workbook?

 

[Variety_Bear]

I opened Vizual Basic in Excel and copied and pasted the VBA Code you have me, it hasn't generated any errors, but hasn't seemed to do anything? What am I doing wrong please? Thanks,

 

[Logit]

If the macro is not providing any information ... then you pasted it in the wrong location.

The macro must be pasted in ThisWorkbook module.

Once the macro is in that location, it WILL function as designed.
(As an aside, make certain you have enabled macros to work in your workbook. That is located in the OPTIONS section of the workbook.)

 

[Variety_Bear]

Thank you for the further help. I still can't get this to work. I'm sorry about this.

Should the macro create a new worksheet with the tracking information? Maybe it is working somewhere but I don't know where the information is?

I have copied your VBA above using the copy clipboard button. Then opened VBA on excel, double clicked 'ThisWorkbook' and pasted the code into it.

It accepts it without errors, but doesn't do anything. Should I be able to run the macro, or does it work in the background?

I've checked my excel settings in the workbook. I have saved the workbook as macro enabled and checked in settings to make sure Macros are enabled.

Thanks again, I really appreciate your help with this.

 

[Variety_Bear]

My VBA is very basic, but what does the 'Tracker' and 'Pricing' bits mean in the VBA? Should I add them as worksheets for this to work?

 

[Logit]

My apologies. I didn't provide sufficient information for you.

In your workbook, create a worksheet name Tracker

I've edited the code to remove some extraneous lines that do not need to be there. Here is the updated macro :

Option Explicit

Dim vOldVal 'Must be at top of module

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

Dim bBold As Boolean

    With Application
         .ScreenUpdating = False
         .EnableEvents = False

    End With

    If IsEmpty(vOldVal) Then vOldVal = "Empty Cell"
    bBold = Target.HasFormula
        With Sheets("Tracker")
            
                If .Range("A1") = vbNullString Then
                    .Range("A1:H1") = Array("Cell Changed", "Old Value", _
                        "New Value", "Old Formula", "New Formula", "Time of Change", "Date of Change", "User")
                End If

            With .Cells(.Rows.Count, 1).End(xlUp)(2, 1)
                  .Value = ActiveSheet.Name & " : " & Target.Address
                  .Offset(0, 1) = vOldVal
            With .Offset(0, 2)
              If bBold = True Then
                .ClearComments
                .AddComment.Text Text:= _
                     "OzGrid.com:" & 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
                .Offset(0, 5) = Application.UserName
            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

If you desire, the worksheet Tracker can be hidden and password protected so only you can view it, if you desire.

 

[Variety_Bear]

Hi Logit, no need for the apologies. You are helping me

That's partly worked, thank you!

It's recording all the information, however, it's entering Time of change into Old Formula, then Date of Change into New formula, Then User in Time of change.
Effectively I think it's skipping old and new formula.

Is there anything you can do about that please?

Good point about the protected and hidden. Noted.

 

[Variety_Bear]

Also, when I do a 'Refresh All', it comes up with an error. 'Run-time error '94':, Invalid use of Null. When I select debug it highlights 'bBold = Target.HasFormula'.

Do you know why it is doing that?