“Log file” of date stamps due to changes in workbook

By : Anders
Source: Stackoverflow.com

I would like to keep track of the changes made in a workbook (DATA). The DATA-workbook are used by several people to share information. Different people fill out the workbook with their relevant informations which in the end are saved automatically. I would like to know how long time each step takes and copy those date stamps into another workbook (LOG), in that way I can see if somebody has forgot to fill out the workbook.

Info          Requestor     Me          Support 
b1001        Kevin         Anders      Support
              04-03-2011    05-03-2011  09-03-2011                
dd1001      Carl          Anders      Support
              05-03-2011    05-03-2011  07-03-2011
hahv500    Steve         Anders      Support

The data above are just a minor part of the DATA-workbook.

The code I'm looking for should open the LOG-workbook and copy in the data and date stamp when somebody has entered their informations..

The requestor e-mails the file to me and then I e-mail it to Support. I suppose that when the requestor e-mails me then row 2 and cell B3 are copied into the LOG-wookbook. When I e-mail it to Support then cell C3 are copied and paste into the LOG and when Support saves it cell D3 are copy/pasted.

I'm looking forward to get a simple answer since I'm not an Excel wizzard.



Thanks for your answer. As far as I can see will that code pop up a message box, if the Target is changed, and I don't need that.

I have this for the first step. I don't know what to do with the next step. If I have to do some lookup... Help is appreciated!

    Dim FirstBlankCell As Range

ActiveSheet.Protect DrawingObjects:=False, Contents:=False, Scenarios:=False
Range("B7") = Now

Workbooks.Open Filename:=Range("Log_destination")

Set FirstBlankCell = Range("A" & Rows.Count).End(xlUp).Offset(1, 0)

ActiveSheet.Range("A6:D6", "A7:B7").Copy
Selection.PasteSpecial Paste:=xlPasteValues
ActiveWorkbook.Close True
By : Anders


I do not have an exatct solution, but you may try to use the WorkBook Events. For example use

Option Explicit 
Private Sub WorkSheet_Change(ByVal Target As Range)
  MsgBox "the value was changed"
End Sub 

and change the action which displays the message to some routine, which would update the LOG worksheet. Of course you may restrict it to work only to changes made on a specifict cells.

Hope it helps.

This video can help you solving your question :)
By: admin