VBA Audit Trail

[CodingMonkey]

I only recently started learning how to use VBA code and I'm come to a standstill with one particular code. I'm not too sure where to start and hope someone here is a miracle worker and can help me!

What I'm aiming to achieve:

A hidden, and preferably locked, worksheet that will record username, time/date, what cell, what they changed it from, what they changed it to. Also if it is possible, maybe have the "cell" option be a hyperlink to quickly jump you to that sheet/cell, but we can start simple.

Information about my document thus far:

I have a workbook with 3 sheets: Calibration Entry Form, Calibrations, Audit Trail.

Calibration Entry Form- it is a blank sheet with a button. You click the button, it opens up a coded user entry form. Currently, for testing purposes, it has five categories that you enter and then click "Add Calibration" and it auto-enters the information into a hidden sheet called Calibrations.

Calibrations - hidden sheet with a table that organizes the compiled data from the entry form into table format. I wanted to be able to lock this sheet as well, but there seems to be an issue that once I lock it, data from the entry form can't be entered. Aka it breaks the only bit of coding I've successfully been able to figure out thus far.

Audit Trail - hidden, preferably locked, sheet that will be a simple table that records any and all changes made to the Calibrations sheet. I would like to see that even data entered from the User Form would get added onto this sheet as well, but I'm not sure if that is possible since it is being entered via another code. Sorry, I'm new to this and don't know the potential.


I do currently work with Office 2007 since the corporation I work for hasn't 100% integrated 2016. Don't know if that is vital or not, but wanted to put that information out there.

Thanks in advance for any help!

 

[Oredo]

Re: VBA Audit Trail Help

Hello CM,

Apologies for late reply, but i was under the bus of different assignments myself, so needed to prioritise. And no apologies needed on your part.
I managed to implement your code in my workbook and it is working perfectly, big THANK YOU for that.
While we at it, i'm looking to improve the code and split old values and new values to separate columns, as people filling information sometimes write "poems" and it might become cumbersome in future for fast tracking.
If you have any ideas, how to achieve that in your code, or if someone else can join the discussion, more merrier as way say.
Once again, thank you for your help CM and if you in Ireland at some point, beer is on me.

 

[CodingMonkey]

Re: VBA Audit Trail Help

Oredo,

I'm glad it worked out for you and everything is firing as planned.

I'm currently working on your request, but I'm running into a hiccup. The PreviousValue won't save within the audit trail, it only shows the new value. Maybe this is an error within my test environment and the code will work perfectly for you. I tried to manipulate the code that I had sent to you for your last request, but ran into too many headaches while trying to edit it here at work. So I wrote a new version that will you will paste within every sheet you want to track changes for, instead of using a module like my prior code. Give it a shot, let me know any errors or issues you run into. In the meantime, I'll continue to work on this on the side when I have free moments at work.

Dim PreviousValue
Private Sub Worksheet_Change(ByVal Target As Range)

Dim i As Long
Dim ws As Worksheet


Set ws = Sheets("Log")


i = ws.Range("A" & Rows.Count).End(xlUp).Row + 1


If Target.Value <> PreviousValue Then
    With ws
        .Range("A" & i).Value = FormatDateTime(Now, vbShortDate)
        .Range("B" & i).Value = FormatDateTime(Now, vbLongTime)
        .Range("C" & i).Value = Environ$("username")
        .Range("D" & i).Value = ActiveSheet.Name & " | " & Target.Address
        .Range("E" & i).Value = PreviousValue
        .Range("F" & i).Value = Target.Value
    End With
End If


End Sub


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    PreviousValue = Target.Value
End Sub

Maybe, in between my attempts, someone with a bit more experience will be able to post a response that has a solution to the small hiccup I'm running into.

Good luck,
Monkey

 

[CodingMonkey]

Re: VBA Audit Trail Help

Oredo,

I'm glad the module is working for you as designed.

I attempted to edit my module to accommodate your requests, but I ran into more headaches then progress while trying to quickly edit the code here at work. I ended up writing a new set of code that is placed within each sheet that you want to track changes. The only hiccup that I'm currently running into is that the PreviousValue is not being recorded. HOPEFULLY, this is just because of the test environment that I'm running the code in.

Give it a shot in your excel workbook - start by placing it within one sheet. Save, close, re-open workbook. Then make test changes to a random cell in the worksheet to see if the code at least tracks into your log. Let me know if you run into any issues, and in the meantime I'll continue to try to work on the code throughout the day when I get spare time at work.

Dim PreviousValue
Private Sub Worksheet_Change(ByVal Target As Range)

Dim i As Long
Dim ws As Worksheet

Set ws = Sheets("Log")

i = ws.Range("A" & Rows.Count).End(xlUp).Row + 1

If Target.Value <> PreviousValue Then
    With ws
        .Range("A" & i).Value = FormatDateTime(Now, vbShortDate)
        .Range("B" & i).Value = FormatDateTime(Now, vbLongTime)
        .Range("C" & i).Value = Environ$("username")
        .Range("D" & i).Value = ActiveSheet.Name & " | " & Target.Address
        .Range("E" & i).Value = PreviousValue
        .Range("F" & i).Value = Target.Value
    End With
End If

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    PreviousValue = Target.Value
End Sub

Good luck, and let me know what your results are. (You can also remove any columns that you don't wish to see. Just make sure to fix the ranges)

Monkey

 

[nr6281]

Re: VBA Audit Trail Help

Hi,

The below code in an amazing way however I need two more changes and I am not sure how to fit it in.

I want to be able to capture to which cell the changed happened to and if a row or colunm was deleted.

How would i do this?

Oredo,

I'm glad the module is working for you as designed.

I attempted to edit my module to accommodate your requests, but I ran into more headaches then progress while trying to quickly edit the code here at work. I ended up writing a new set of code that is placed within each sheet that you want to track changes. The only hiccup that I'm currently running into is that the PreviousValue is not being recorded. HOPEFULLY, this is just because of the test environment that I'm running the code in.

Give it a shot in your excel workbook - start by placing it within one sheet. Save, close, re-open workbook. Then make test changes to a random cell in the worksheet to see if the code at least tracks into your log. Let me know if you run into any issues, and in the meantime I'll continue to try to work on the code throughout the day when I get spare time at work.

Dim PreviousValue
Private Sub Worksheet_Change(ByVal Target As Range)

Dim i As Long
Dim ws As Worksheet

Set ws = Sheets("Log")

i = ws.Range("A" & Rows.Count).End(xlUp).Row + 1

If Target.Value <> PreviousValue Then
    With ws
        .Range("A" & i).Value = FormatDateTime(Now, vbShortDate)
        .Range("B" & i).Value = FormatDateTime(Now, vbLongTime)
        .Range("C" & i).Value = Environ$("username")
        .Range("D" & i).Value = ActiveSheet.Name & " | " & Target.Address
        .Range("E" & i).Value = PreviousValue
        .Range("F" & i).Value = Target.Value
    End With
End If

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    PreviousValue = Target.Value
End Sub

Good luck, and let me know what your results are. (You can also remove any columns that you don't wish to see. Just make sure to fix the ranges)

Monkey

 

[nr6281]

Re: VBA Audit Trail Help

Instead of cell I would like to know the column to which the change was made