to report change in wrksheet in another sheet, not usin track chan

[Epoch]

Hi all,

This is a sort of a follow up question for one posted on 22nd May 2006. The
original question and it's two most important replies are as follows:

Original:

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. '

Reply 1:

'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


Reply 2 which is explaining what the above code does in the user's
spreadsheet:

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

----------------------------------------------------------------------------------------

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)

Also I don't think track changes can produce a column sppecific report like
the above code does. if it can please let me know.

Anyways, please help me if you can... desperately need this one

Thanks a lot in advance,

Epoch