software

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.

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(quot;A2quot;)) Is Nothing Then
On Error GoTo ErrHandler
Application.EnableEvents = False
With Worksheets(quot;Logquot;)
With .Cells(.Rows.Count, 1).End(xlUp).Offset( _
1, 0).Resize(1, 3)
With .Item(1)
.NumberFormat = quot;dd mmm yyyy hh:mm:ssquot;
.Value = Now
End With
.Item(2) = Application.UserName
.Item(3) = Me.Range(quot;A2quot;).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 gt;,
quot;jiwolfquot; gt; wrote:

gt; Is it possible to record all changes to a cell within a workbook?
gt;
gt; For example any changes that are made bu users of a workbook to cell A2?
gt;
gt; TIA.

thanks, but how do i quot;activatequot; it? and where will it store the entered
data? i need to be able to go back and examine all of the data entered.quot;JE McGimpseyquot; gt; wrote in message
...
gt; One way:
gt;
gt; Put this in your worksheet code module (right-click the worksheet tab
gt; and choose View Code):
gt;
gt; Private Sub Worksheet_Change(ByVal Target As Excel.Range)
gt; With Target
gt; If Not Intersect(.Cells, Range(quot;A2quot;)) Is Nothing Then
gt; On Error GoTo ErrHandler
gt; Application.EnableEvents = False
gt; With Worksheets(quot;Logquot;)
gt; With .Cells(.Rows.Count, 1).End(xlUp).Offset( _
gt; 1, 0).Resize(1, 3)
gt; With .Item(1)
gt; .NumberFormat = quot;dd mmm yyyy hh:mm:ssquot;
gt; .Value = Now
gt; End With
gt; .Item(2) = Application.UserName
gt; .Item(3) = Me.Range(quot;A2quot;).Value
gt; End With
gt; End With
gt; ErrHandler:
gt; Application.EnableEvents = True
gt; On Error GoTo 0
gt; End If
gt; End With
gt; End Sub
gt;
gt; Change the name of your log sheet to suit.
gt;
gt;
gt; In article gt;,
gt; quot;jiwolfquot; gt; wrote:
gt;
gt;gt; Is it possible to record all changes to a cell within a workbook?
gt;gt;
gt;gt; For example any changes that are made bu users of a workbook to cell A2?
gt;gt;
gt;gt; TIA.
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 quot;your log
sheetquot;, which I named quot;Logquot; in the macro. If you wish to use a different
sheet, change the name to your desired sheet in the macro.

In article gt;,
quot;jiwolfquot; gt; wrote:

gt; thanks, but how do i quot;activatequot; it? and where will it store the entered
gt; data? i need to be able to go back and examine all of the data entered.

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.

quot;JE McGimpseyquot; gt; wrote in message
...
gt; You don't activate it - it's an event macro, which is why I gave you
gt; instructions on where to put it. Once there, it will fire whenever a
gt; change (i.e., manual or remote entry in a cell) is made to the worksheet.
gt;
gt; I implied, but didn't state, that the data would be stored in quot;your log
gt; sheetquot;, which I named quot;Logquot; in the macro. If you wish to use a different
gt; sheet, change the name to your desired sheet in the macro.
gt;
gt; In article gt;,
gt; quot;jiwolfquot; gt; wrote:
gt;
gt;gt; thanks, but how do i quot;activatequot; it? and where will it store the entered
gt;gt; data? i need to be able to go back and examine all of the data entered.
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 quot;Logquot;

In article gt;,
quot;jiwolfquot; gt; wrote:

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

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
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: www.mvps.org/dmcritchie/excel/excel.htm
Search Page: www.mvps.org/dmcritchie/excel/search.htm

quot;jiwolfquot; gt; wrote in message ...
gt; Then perhaps i'm being a bit dumb. I followed your instructions, creating
gt; a new workbook and inserted the code,but the macro doesn't seem to fire.
gt;
gt;
gt;
gt;
gt; quot;JE McGimpseyquot; gt; wrote in message
gt; ...
gt; gt; You don't activate it - it's an event macro, which is why I gave you
gt; gt; instructions on where to put it. Once there, it will fire whenever a
gt; gt; change (i.e., manual or remote entry in a cell) is made to the worksheet.
gt; gt;
gt; gt; I implied, but didn't state, that the data would be stored in quot;your log
gt; gt; sheetquot;, which I named quot;Logquot; in the macro. If you wish to use a different
gt; gt; sheet, change the name to your desired sheet in the macro.
gt; gt;
gt; gt; In article gt;,
gt; gt; quot;jiwolfquot; gt; wrote:
gt; gt;
gt; gt;gt; thanks, but how do i quot;activatequot; it? and where will it store the entered
gt; gt;gt; data? i need to be able to go back and examine all of the data entered.
gt;
gt;