record all changes to a cell?

J

jiwolf

Is it possible to record all changes to a cell within a workbook?

For example any changes that are made bu users of a workbook to cell A2?

TIA.
 
J

JE McGimpsey

One way:

Put this in your worksheet code module (right-click the worksheet tab
and choose View Code):

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If Not Intersect(.Cells, Range("A2")) Is Nothing Then
On Error GoTo ErrHandler
Application.EnableEvents = False
With Worksheets("Log")
With .Cells(.Rows.Count, 1).End(xlUp).Offset( _
1, 0).Resize(1, 3)
With .Item(1)
.NumberFormat = "dd mmm yyyy hh:mm:ss"
.Value = Now
End With
.Item(2) = Application.UserName
.Item(3) = Me.Range("A2").Value
End With
End With
ErrHandler:
Application.EnableEvents = True
On Error GoTo 0
End If
End With
End Sub

Change the name of your log sheet to suit.
 
J

jiwolf

thanks, but how do i "activate" it? and where will it store the entered
data? i need to be able to go back and examine all of the data entered.
 
J

JE McGimpsey

You don't activate it - it's an event macro, which is why I gave you
instructions on where to put it. Once there, it will fire whenever a
change (i.e., manual or remote entry in a cell) is made to the worksheet.

I implied, but didn't state, that the data would be stored in "your log
sheet", which I named "Log" in the macro. If you wish to use a different
sheet, change the name to your desired sheet in the macro.
 
J

jiwolf

Then perhaps i'm being a bit dumb. I followed your instructions, creating
a new workbook and inserted the code,but the macro doesn't seem to fire.
 
J

JE McGimpsey

You can download this test workbook:

ftp://ftp.mcgimpsey.com/excel/jiwolf.demo.xls

Make some changes in cell A2 on Sheet1, then switch over to sheet "Log"
 
D

David McRitchie

Hi Peter,
The instructions for installing an Event Macro differ and were given as::
Put this in your worksheet code module (right-click the worksheet tab
and choose View Code):
which would be the same workbook, and right-click on the same worksheet tab
as you are in. You will see this in the workbook John sends you. In the VBE
(Visual Basic Editor) you will see the code in a Sheet1 (for example) instead of
in Module1 (for example).

If you have any additional problems with the Event Macro see
http://www.mvps.org/dmcritchie/excel/event.htm#problems
A sheet event macro applies only to the one worksheet.
 
Top