record all changes to a cell?
 

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.

record all changes to a cell?
 

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.


In article ,
"jiwolf" wrote:

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.

record all changes to a cell?
 

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.


"JE McGimpsey" wrote in message
...
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.


In article ,
"jiwolf" wrote:

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.

record all changes to a cell?
 

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.

In article ,
"jiwolf" wrote:

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.

record all changes to a cell?
 

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.




"JE McGimpsey" wrote in message
...
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.

In article ,
"jiwolf" wrote:

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.

record all changes to a cell?
 

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"

In article ,
"jiwolf" wrote:

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.

record all changes to a cell?
 

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.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"jiwolf" wrote in message ...
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.




"JE McGimpsey" wrote in message
...
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.

In article ,
"jiwolf" wrote:

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.