Solved

MS Excel Audit Trail Log

Posted on 2012-04-03
8
3,282 Views
Last Modified: 2012-08-13
Hello,

I would like to have VB script added to the attached worksheet that will allow me to automatically track by running a consistent audit trail log on the second spreadsheet of all changes made to the first spreadsheet.

I would like the following tracked.
Date and time of the addition/change/deletions
The username (as stated in the Excel options tab)
An explanation of the additions/changes/deletions

Also, I would like to know can the cells on the audit log be locked by a password, so other users of the spreadsheet will not be able to manually change any of the information?

Looking forward to your responses. Thank you so much -- Chris
Audit-Trail-Log-WB-v1.xlsx
0
Comment
Question by:ckwillGWU
  • 4
  • 4
8 Comments
 

Author Comment

by:ckwillGWU
ID: 37806295
I found this link, but is was slightly confusing.

http://excelexperts.com/VBA-Tips-Log-An-Audit-Trail
0
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 37806744
Try the simple code below from this link:
       http://www.excelforum.com/excel-programming/730856-doing-an-audit-trail-on-multiple-sheets.html  

If you need more then what the simple code below provides, then go back to the link you found and try to take one segment at time.  IMHO, the link is a thorough and very comprehensive presentation of a complex subject in tutorial fashion, and shows how a variety of different requirements (some of which you may not  need or want) can be handled:  

http://excelexperts.com/VBA-Tips-Log-An-Audit-Trail


_________________________________________________________
Note: You need to insert the code in each sheet:

Dim PreviousValue

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Value <> PreviousValue Then
        Sheets("log").Cells(65000, 1).End(xlUp).Offset(1, 0).Value = _
            Application.UserName & " - sheet: " & ActiveSheet.Name _
            & " - changed cell " & Target.Address _
            & " from " & PreviousValue & " to " & Target.Value
    End If
End Sub

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

Assisted Solution

by:puppydogbuddy
puppydogbuddy earned 500 total points
ID: 37806820
See also this link. It contains code for password protection.

http://excelexperts.com/lock-cells-worksheet-after-they-are-changed
0
 

Author Comment

by:ckwillGWU
ID: 37807137
OK! I entered the code on spreadsheets; however I'm not sure if I was suppose to update or change any of the text, but I did test and no log was created. I've attached my attempt.
Audit-Trail-Log-WB-vm1.xlsm
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 38

Assisted Solution

by:puppydogbuddy
puppydogbuddy earned 500 total points
ID: 37812153
Chris,
Just got back online and took a quick look at your test file and could not immediately determine out why it isn't working.  Funny thing is I downloaded the sample file from the tutorial link (http://excelexperts.com/VBA-Tips-Log-An-Audit-Trail), which uses the same code and it works.  I checked the obvious things like the references and security level and they appear to be ok.  The only apparent differences I noted between the the tutorial file (which works) and your test file (which is not working) are the following:
      1. Your file was created using Excel 2007; the tutorial file and my upload of your test file were saved in a pre-2007 format.  
       2. the VBA project view in the vb editor shows that you have 4 modules vs one module for the tutorial file.  One module appears to be all that is needed in this case because both sheets and the workbook all use the same code.                                            

I will look into it in greater detail later tonight.  In the meantime you should upload the tutorial file that is refered to above, and see if it  works  on your computer.
0
 

Author Comment

by:ckwillGWU
ID: 37812316
Thanks PuppyDogBuddy,

I actually downloaded the file yesterday. For the date and time addition, I'm going to try a workaround and do a nested IF statement to add the date and time to column B on the log sheet. Then, I hide columns A & B and Merge all the information to show Date, Time & Change in Column C. I'm pretty sure this will slow up the spreadsheet a little, but, it is the only thing that I can think of at the moment.
0
 

Author Comment

by:ckwillGWU
ID: 37814585
I've attached the working spreadsheet that has many issues; however for this post, I'm only going to focus on Issue #1 as found in the Desc tab of the attached workbook.

To solve issue 1, I attempted the code from the following link http://www.excelforum.com/excel-general/761359-insert-permanent-date-and-time-stamp-if-and-when-another-cell-is-populated.html

I updated the range, but I'm not sure if it is correct. The code was entered on the log tab.

Can you see what I did wrong here for this not to work properly? Or, do you know how I can fix it?
Test-Report-v2.xlsm
0
 
LVL 38

Accepted Solution

by:
puppydogbuddy earned 500 total points
ID: 37816355
Chris,
I have excel 2003 + the compatibility pack for conversion of 2007, but for some reason it could not convert your latest file and I can only view it in read-only mode.  If you could attach a 2003 copy of your file,  I could tinker with it.

Just looking at what you have, I would use the DateAdd function, as discussed in the following link, for date incrementation and manipulation.
    http://www.excelhelpdesk.com/functions/dateadd-function/

And you certainly can use nested ifs in excel, but you will have to play around the different syntaxes shown in the links below to find one that works.
          http://en.allexperts.com/q/Excel-1059/syntax-question.htm
          http://www.techonthenet.com/excel/formulas/if_nested.php
          http://www.excelforum.com/excel-general/560752-comparing-dates-with-nested-if-not-working-syntax-is-correct-though.html
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Input Macro 8 22
splitting text of cell to columns 14 22
excel connection properties parameters grayed out 5 24
Filling Blank Cells 14 8
Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …

947 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now