Track / Record changes in Excel via Background Macro.

[brites]

Hi Folks!
Once again I have no clue where to start!
Im preparing a module to teach my team the very basics of excel. As part of the module, Im sending some "homework" for them. Here's the deal: Is there anyway for me to set a Macro that would record their actions? I really want to see if they did on their own or if they copied from each other.

Does anyone have any ideas on how to control it?

Thx! in advance,
Brites.

 

[Jonmo1]

You could probably use Workbook level SheetChange Events...

Create a New Sheet, name it LogSheet(or whatever)
Put in headers like
A1 = Range Changed
B1 = Date/Time
C1 = Value Entered

Hide that sheet (you can even use the xlveryhidden property)..

put this code in the ThisWorkbook Module
Right click the excel Icon at top left by "File - Edit - etc.." View Code
Paste the following

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim LR As Long
Dim c As Range
Application.EnableEvents = False
With Sheets("LogSheet")
    For Each c In Target
    LR = .Cells(Rows.count, "A").End(xlUp).Row + 1
    .Cells(LR, "A").Value = Sh.Name & "!" & c.Address
    .Cells(LR, "B").Value = Now
    .Cells(LR, "C").Value = c.Value
    Next c
End With
Application.EnableEvents = True
End Sub

This will log every change made..Just hope it will not exceed 65336 changes, as it will get wierd after that...There's only 65536 Rows in excel. Unless you're in XL2007.

Hope that helps..

 

[brites]

Jonmo1, thanks again for helping me.
Your answers are always simple and effective.

This one is awsome already, but I need something else.
Can I improve it? If the cell was copied from another workbook, I'd reaaly like to see it. Not only the value but the source and all. Is there a way of doing that?

Thanks!

 

[Jonmo1]

Sorry, don't think so.

But if he idea is to catch someone copying someone else's work, it's gonna be a judgement call.

If 1 person spends hours changing different cells at different times, but someone else changes ALOT of Cells at the same time(or close), then they probably copied it.

 

[brites]

Yep, thats true.
Nice tip, thanks.

 

[Legacy 98055]

You could determine the source workbook if the copy and paste are completed within Excel. Would that be of any use to you?

For example, one of the paste options in Excel is "Paste as Link". In order for Excel to paste a link, it must carry information about the source on the clipboard. I copied cell A1 from sheet1 in book CopyClipBoardFunctions.xls and this, among a gazillion other things, was on the clipboard...

SourceURL:file:///C:\Documents%20and%20Settings\TJS\Desktop\CopyClipBoardFunctions.xls

You could check the clipboard at the change event and determine the source. Would this work for you?

Here is everything else that was on the clipboard for a one cell copy operation.

--- content removed ---

 

[Ritz]

One additional thing you can do (which I do w/my students for Excel) -- I turn off the ability for them to copy/paste in the homework spreadsheet. It's a pain, but then I know they've actually entered the information. It doesn't mean they aren't re-typing information from someone else, but at least they're going to have a painful "copy" experience.

The copy/paste works everywhere else -- just on on their assignment.

 

[pat_gilly]

Hi, I need only changes in columns a c f m in sheet 1 recorded in the logsheet, maybe in columns abcd.But I need to capture the users lanID too, Can jonmo1 or anybody help me.

 

[leroy79]

You could probably use Workbook level SheetChange Events...

Create a New Sheet, name it LogSheet(or whatever)
Put in headers like
A1 = Range Changed
B1 = Date/Time
C1 = Value Entered

Hide that sheet (you can even use the xlveryhidden property)..

put this code in the ThisWorkbook Module
Right click the excel Icon at top left by "File - Edit - etc.." View Code
Paste the following

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim LR As Long
Dim c As Range
Application.EnableEvents = False
With Sheets("LogSheet")
    For Each c In Target
    LR = .Cells(Rows.count, "A").End(xlUp).Row + 1
    .Cells(LR, "A").Value = Sh.Name & "!" & c.Address
    .Cells(LR, "B").Value = Now
    .Cells(LR, "C").Value = c.Value
    Next c
End With
Application.EnableEvents = True
End Sub

This will log every change made..Just hope it will not exceed 65336 changes, as it will get wierd after that...There's only 65536 Rows in excel. Unless you're in XL2007.

Hope that helps..

How would you record the "old" value, say in column D, in this code? I'm pretty new and learning, Thanks in advance for the help!!

 

[antoinezerafa]

i have a sheet with checkboxes which i need to track the time they are being checked. what shall i add to track checkboxes

You could probably use Workbook level SheetChange Events...

Create a New Sheet, name it LogSheet(or whatever)
Put in headers like
A1 = Range Changed
B1 = Date/Time
C1 = Value Entered

Hide that sheet (you can even use the xlveryhidden property)..

put this code in the ThisWorkbook Module
Right click the excel Icon at top left by "File - Edit - etc.." View Code
Paste the following

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim LR As Long
Dim c As Range
Application.EnableEvents = False
With Sheets("LogSheet")
    For Each c In Target
    LR = .Cells(Rows.count, "A").End(xlUp).Row + 1
    .Cells(LR, "A").Value = Sh.Name & "!" & c.Address
    .Cells(LR, "B").Value = Now
    .Cells(LR, "C").Value = c.Value
    Next c
End With
Application.EnableEvents = True
End Sub

This will log every change made..Just hope it will not exceed 65336 changes, as it will get wierd after that...There's only 65536 Rows in excel. Unless you're in XL2007.

Hope that helps..