LOG creation in excel for changes on sheet

[mukhtarhi]

I have a small piece of data in cell A1 to B5.
[TABLE="width: 96"]
<COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><TBODY>[TR]
[TD="width: 64, bgcolor: transparent"]Name[/TD]
[TD="width: 64, bgcolor: transparent"]Marks[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Simon[/TD]
[TD="bgcolor: transparent, align: right"]180[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Johnny[/TD]
[TD="bgcolor: transparent, align: right"]250[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Chicken[/TD]
[TD="bgcolor: transparent, align: right"]500[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Meow[/TD]
[TD="bgcolor: transparent, align: right"]160[/TD]
[/TR]
</TBODY>[/TABLE]

Is there any way I can create a log if say this is a shared excel sheet or not a shared excel sheet some other user checks the file and changes a few things for eg : the figure for Johnny is 250 the other user changes it to 600, how can I get a log of which users have changed what part of the data, is there any formula or anything i can keep hidden so i can run and come to know who changed what, i know that password protection and locking of cells can be done by i want to create a log.

 

[AOB]

Hi mukhtarhi,

This might get you started - you can play around with it to get it to do what you want it to do...

Create a separate sheet called 'Log' with 4 column headers :

• User (who made the change?)
• Change Made To (what cell did they change?)
• Value Changed To (what did they change it to?)
• Timestamp (when did they change it?)

Add this VBA code to the sheet containing the data you want to track (not the newly created Log sheet...)

Option Explicit

Const intUsernameColumn = 1
Const intCellRefColumn = 2
Const intNewValueColumn = 3
Const intTimestampColumn = 4

Private Sub Worksheet_Change(ByVal Target As Range)[INDENT]
Dim shtLog As Worksheet
Dim cll As Variant
Dim lngNextRow As Long

Set shtLog = ThisWorkbook.Sheets("Log")

For Each cll In Target.Cells


[/INDENT]
[INDENT=2]lngNextRow = shtLog.Cells.Find(What:="*", After:=[A1], Searchorder:=xlByRows, SearchDirection:=xlPrevious).Row + 1

shtLog.Cells(lngNextRow, intUsernameColumn).Value = Environ("username")
shtLog.Cells(lngNextRow, intCellRefColumn).Value = cll.Address
shtLog.Cells(lngNextRow, intNewValueColumn).Value = cll.Value
shtLog.Cells(lngNextRow, intTimestampColumn).Value = Format(Now, "dd-mmm-yy hh:mm:ss")[/INDENT]
[INDENT]
Next cll


[/INDENT]
End Sub

Bear in mind, users may have to enable macros on opening the workbook to ensure this actually kicks in (if trust centre settings require a prompt)

Hope it gets you started anyway - not sure how to do it without invoking VBA (i.e. formulas etc.)

AOB

 

[p45cal]

rather than re-invent the wheel, there's a variety of scenarios amongst the links below:
Daily Dose of Excel » Blog Archive » Log Worksheet Changes
Track changes in a shared workbook - Excel - Office.com
Log changes to Excel file
Can I log changes in an Excel spreadsheet? - Yahoo! Answers
Track Changes in Excel 2007 - YouTube
Excel Macros : Log User Activity to Log Sheet ~ Yogesh Gupta's Excel Tips
Excel Track Changes. Tracking Changes in Excel
How to Track Changes in Excel 2007 - Scribendi.com

 

[AOB]

Some nice links p45cal - my response was on the basis of the source being "a small piece of data" in which case a quick Worksheet_Change based approach should suffice. Agree that for larger populations a more efficient approach may be called for...

 

[mukhtarhi]

@ p45cal - actually my PC crashed so unfirtunately i couldnt reply you back i will try the link and reply you at earliest.
@ AOB - i tried the Macro it ain't working do know what further actions i can take on it i tried to on every way but it is not showing me the macro and gives errors when i actuallly go line by line in the VBA.?

 

[mukhtarhi]

@ p45cal - many thanks it helps...cheers all link makes sense since am using 2007

 

[Brandrup]

Hi AOB

How do you change the range/target (I know very little about VBA) of the code? If I add a new row this code will create around 16000 new entries to the log. I would like to only log changes made in fx column A to L.

Best regards
Brandrup

Hi mukhtarhi,

This might get you started - you can play around with it to get it to do what you want it to do...

Create a separate sheet called 'Log' with 4 column headers :

• User (who made the change?)
• Change Made To (what cell did they change?)
• Value Changed To (what did they change it to?)
• Timestamp (when did they change it?)

Add this VBA code to the sheet containing the data you want to track (not the newly created Log sheet...)

Option Explicit

Const intUsernameColumn = 1
Const intCellRefColumn = 2
Const intNewValueColumn = 3
Const intTimestampColumn = 4

Private Sub Worksheet_Change(ByVal Target As Range)[INDENT]
Dim shtLog As Worksheet
Dim cll As Variant
Dim lngNextRow As Long

Set shtLog = ThisWorkbook.Sheets("Log")

For Each cll In Target.Cells


[/INDENT]
[INDENT=2]lngNextRow = shtLog.Cells.Find(What:="*", After:=[A1], Searchorder:=xlByRows, SearchDirection:=xlPrevious).Row + 1

shtLog.Cells(lngNextRow, intUsernameColumn).Value = Environ("username")
shtLog.Cells(lngNextRow, intCellRefColumn).Value = cll.Address
shtLog.Cells(lngNextRow, intNewValueColumn).Value = cll.Value
shtLog.Cells(lngNextRow, intTimestampColumn).Value = Format(Now, "dd-mmm-yy hh:mm:ss")[/INDENT]
[INDENT]
Next cll


[/INDENT]
End Sub

Bear in mind, users may have to enable macros on opening the workbook to ensure this actually kicks in (if trust centre settings require a prompt)

Hope it gets you started anyway - not sure how to do it without invoking VBA (i.e. formulas etc.)

AOB