How can I track changes to a cell in Excel?

[Guest]

I have a spreadsheet used to track actions by numerous folks. There is a
column for a due date that I would like to see when and how often it is
changed. I looked at Track Changes, but it tracks ALL changes in all cells
(adds, edits etc) - which is too much info. I would like to see only the
changes on a particular column after the initial entry. Any thoughts would
be greatly appreciated.

 

[Ardus Petus]

Here is one solution: http://cjoint.com/?fwk4yzHeiZ

You'll have to change the constants in the event proc to suit your needs.

Following code should be placed in worksheet's code:

HTH
--
AP

'-------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
'Column to be watched
Const sWatch As String = "F"
'Column of reference data that will show on Track sheet
Const sRef As String = "A"

Dim rWatch As Range
Dim rCell As Range
Dim sUser As String
Dim lOffset As Long

Set rWatch = Intersect(Target, Columns(sWatch))
If rWatch Is Nothing Then Exit Sub
sUser = Environ("username")
lOffset = Columns(sRef).Column - Columns(sWatch).Column
With Worksheets("Track")
For Each rCell In rWatch
With .Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
.Value = rCell.Offset(0, lOffset)
.Offset(0, 1).Value = Now
.Offset(0, 2).Value = sUser
.Offset(0, 3).Value = rCell.Value
End With
Next rCell
End With
'------------------------------
End Sub

 

[JimMay]

Ardus:
I'm fairly new to VBA... I understand your
With Worksheets("Track") << Worksheets("Track") being the object
But what is the object of the line
With .Cells(Rows.Count, "A").End(xlUp).Offset(1, 0) << The With speaks
to what?
Thanks in advance,

 

[Ken Johnson]

Hi Jim,

The previous, With (With Worksheets("Track"), gives you the object for
With .Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)

Excel reads it as...

With Worksheets("Track").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)

Ken Johnson

 

[Ken Johnson]

Hi Jim,

My punctuation went a touch awry. It should have read...

The previous With, With Worksheets("Track"), gives you the object for
With .Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)

Excel reads it as...

With Worksheets("Track").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)

Ken Johnson

 

[JimMay]

Thanks Ken,
Appreciate the explanation.
So the 2nd With refers is the same as the first;
Both referring to the Worksheet Track;
Much obliged,
Jim

 

[Ken Johnson]

You're welcome Jim.
Ken Johnson

 

[Guest]

Ken:

Thanks for the response. I am a VBA virgin...ok with using the Macro
Recorder and creating a button or two....but this is new territory for me.
It looks like I am gonna need to read up on this to better understand how to
use VBA. Could you suggest any good references?

Thanks again.

 

[Ken Johnson]

Hi Ukyankee,

Ardus Petus posted the response, however...

to get the code in place...

1. Copy it, starting at "Private Sub Worksheet_Change(ByVal Target As
Range)" and finishing at "End Sub" (inclusive)

2. In your workbook right click the sheet tab of the sheet with the
data column you are wanting tracked.

3. Select "View Code" from the popup menu. This takes you to the
sheet's code module.

4. Paste the code into the code module (white space)

5. The code refers to the column being tracked - Const sWatch As String
= "F"

Change the "F" to the column you are wanting tracked.

6. The code refers to a reference column - Const sRef As String = "A"

Change the "A" to the column you are wanting to appear associated with
the tracked column

7. Press Alt + F11 to return to the sheet

8. The code refers to a sheet named "Track" so you will have to name a
spare sheet in your workbook "Track" for the code to work.

The code is an example of an Event Procedure, macros that are
automatically triggered by certain changes that occur during the
operation of Excel. This Event procedure is triggered every time the
user changes a cell's value on the sheet with the code in its code
module.


Do a Google search eg "VBA Books" to find some titles.
I started out with John Walkenback's "Excel 97 Programming for Dummies"
and John Green's "Excel 2000 VBA Programmer's Reference" (Wrox Press)

Ken Johnson

 

[Ardus Petus]

Thanks for the explanations: I saved them so that I can re-use them.

 

[Ken Johnson]

You're welcome Ardus.

Ken Johnson

 

[Guest]

Ken & Ardus -

Thank you for your help, excellent explanations, and suggestions on VBA
learning. This is my first experience using this resource...and I must say
that I am impressed by your technical expertise and willingness to assist
others.

Bon jour et merci.

 

[Ken Johnson]

Thanks for those kind words Ukyankee.

Ken Johnson

 

[Ardus Petus]

I'm glad I could help!

(in the future, I will abbreviate that as IGICH)

 

[Guest]

Ardus - I have added the event macro and it is working nicely. In fact, it
is working so well, I would like to add some further information.

The Column of reference data that shows on Track sheet (now called
("DUEDATE-CONT COMPLIANCE") is "A". It would be helpful to display
additional columns on the Track Sheet. I tried copying the line {Const sRef
As String = "A"}, changing the "A" to "B", and adding it below the original
line - but received an error message. Can you suggest a change to the macro
that would accomplish this.

The current macro looks like...

Private Sub Worksheet_Change(ByVal Target As Range)
'Column to be watched
Const sWatch As String = "J"
'Column of reference data that will show on Track sheet
Const sRef As String = "A"

Dim rWatch As Range
Dim rCell As Range
Dim sUser As String
Dim lOffset As Long

Set rWatch = Intersect(Target, Columns(sWatch))
If rWatch Is Nothing Then Exit Sub
sUser = Environ("username")
lOffset = Columns(sRef).Column - Columns(sWatch).Column
With Worksheets("DUEDATE-CONT COMPLIANCE")
ActiveSheet.Unprotect
For Each rCell In rWatch
With .Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
.Value = rCell.Offset(0, lOffset)
.Offset(0, 1).Value = Now
.Offset(0, 2).Value = sUser
.Offset(0, 3).Value = rCell.Value
ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True _
, AllowFormattingColumns:=True, AllowFormattingRows:=True, _
AllowInsertingRows:=True, AllowDeletingColumns:=True,
AllowDeletingRows:= _
True, AllowSorting:=True, AllowFiltering:=True
End With

 

[Guest]

Ken - I have added the event macro and it is working nicely. In fact, it
is working so well, I would like to add some further information.

The Column of reference data that shows on Track sheet (now called
("DUEDATE-CONT COMPLIANCE") is "A". It would be helpful to display
additional columns on the Track Sheet associated with the column I am tracking. I tried copying the line {Const sRef As String = "A"}, changing the "A" to "B", and adding it below the original
line - but received an error message. Can you suggest a change to the macro
that would accomplish this.

The current macro looks like...

Private Sub Worksheet_Change(ByVal Target As Range)
'Column to be watched
Const sWatch As String = "J"
'Column of reference data that will show on Track sheet
Const sRef As String = "A"

Dim rWatch As Range
Dim rCell As Range
Dim sUser As String
Dim lOffset As Long

Set rWatch = Intersect(Target, Columns(sWatch))
If rWatch Is Nothing Then Exit Sub
sUser = Environ("username")
lOffset = Columns(sRef).Column - Columns(sWatch).Column
With Worksheets("DUEDATE-CONT COMPLIANCE")
ActiveSheet.Unprotect
For Each rCell In rWatch
With .Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
.Value = rCell.Offset(0, lOffset)
.Offset(0, 1).Value = Now
.Offset(0, 2).Value = sUser
.Offset(0, 3).Value = rCell.Value
ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True _
, AllowFormattingColumns:=True, AllowFormattingRows:=True, _
AllowInsertingRows:=True, AllowDeletingColumns:=True,
AllowDeletingRows:= _
True, AllowSorting:=True, AllowFiltering:=True
End With
"

 

[Epoch]

Hi Ken,

This is a reply to a post you've made on 22nd May 2006. Your post was a
reply to another post which is as follows:

'Here is one solution: http://cjoint.com/?fwk4yzHeiZ

You'll have to change the constants in the event proc to suit your needs.

Following code should be placed in worksheet's code:

HTH
--
AP

'-------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
'Column to be watched
Const sWatch As String = "F"
'Column of reference data that will show on Track sheet
Const sRef As String = "A"

Dim rWatch As Range
Dim rCell As Range
Dim sUser As String
Dim lOffset As Long

Set rWatch = Intersect(Target, Columns(sWatch))
If rWatch Is Nothing Then Exit Sub
sUser = Environ("username")
lOffset = Columns(sRef).Column - Columns(sWatch).Column
With Worksheets("Track")
For Each rCell In rWatch
With .Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
.Value = rCell.Offset(0, lOffset)
.Offset(0, 1).Value = Now
.Offset(0, 2).Value = sUser
.Offset(0, 3).Value = rCell.Value
End With
Next rCell
End With
'------------------------------
End Sub

I have a spreadsheet used to track actions by numerous folks. There is a
column for a due date that I would like to see when and how often it is
changed. I looked at Track Changes, but it tracks ALL changes in all
cells
(adds, edits etc) - which is too much info. I would like to see only the
changes on a particular column after the initial entry. Any thoughts
would
be greatly appreciated. '

Now, I have a similar query, only that instead of one column there are a few
more. Its a spreadsheet with say products and their ID's and a final column
with no. of customers who have purchased that product. now, the number of
customers for each product could change, but also there could be new products
added to this sheet. At the end of each month I would like to see a separate
sheet (like the sheet named 'Track' in the above code) which highlights
changes in the 'customers' tab, and also in the product tab (to highlight the
new products entered in that month).
If I could have changes in say the 'product' and 'ID' columns in one sheet
and the changes in 'customer' column in another sheet it would solve another
issue although it's not very important.
I was thinking that adding another column being tracked along side 'F' would
work but since I don't know VBA, it's probably not the solution (it gave an
error on compiling 'Column to be watched
Const sWatch As String = "F", "G".....the "G" was my addition to the code)

Anyways, please help me if you can... looking at your and Ardus' posts I
thought it's worth a shot!

Thanks a lot in advance,

Epoch

 

[Epoch]

Hi Ardus,

This is a reply to a post you've made on 22nd May 2006. Your post was a
reply to another post both of which are as follows:

Here is one solution: http://cjoint.com/?fwk4yzHeiZ

You'll have to change the constants in the event proc to suit your needs.

Following code should be placed in worksheet's code:

HTH
--
AP

'-------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
'Column to be watched
Const sWatch As String = "F"
'Column of reference data that will show on Track sheet
Const sRef As String = "A"

Dim rWatch As Range
Dim rCell As Range
Dim sUser As String
Dim lOffset As Long

Set rWatch = Intersect(Target, Columns(sWatch))
If rWatch Is Nothing Then Exit Sub
sUser = Environ("username")
lOffset = Columns(sRef).Column - Columns(sWatch).Column
With Worksheets("Track")
For Each rCell In rWatch
With .Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
.Value = rCell.Offset(0, lOffset)
.Offset(0, 1).Value = Now
.Offset(0, 2).Value = sUser
.Offset(0, 3).Value = rCell.Value
End With
Next rCell
End With
'------------------------------
End Sub

Now, I have a similar query, only that instead of one column there are a few
more. Its a spreadsheet with say products and their ID's and a final column
with no. of customers who have purchased that product. now, the number of
customers for each product could change, but also there could be new products
added to this sheet. At the end of each month I would like to see a separate
sheet (like the sheet named 'Track' in the above code) which highlights
changes in the 'customers' tab, and also in the product tab (to highlight the
new products entered in that month).
If I could have changes in say the 'product' and 'ID' columns in one sheet
and the changes in 'customer' column in another sheet it would solve another
issue although it's not very important.
I was thinking that adding another column being tracked along side 'F' would
work but since I don't know VBA, it's probably not the solution (it gave an
error on compiling 'Column to be watched
Const sWatch As String = "F", "G".....the "G" was my addition to the code)

Anyways, please help me if you can... looking at yours and Ken's posts I
thought it's worth a shot!

Thanks a lot in advance,

Epoch


P.S.: Also if you need to see what another poster named Ken Johnson posted
as a reply to your post, here it goes: