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.

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

"Ukyankee" a écrit dans le message de
news: ...
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:
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,


"Ardus Petus" wrote in message
:

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

"Ukyankee" a écrit dans le message de
news: ...
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.

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

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

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" wrote in message
oups.com:

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

You're welcome Jim.
Ken Johnson

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" wrote:

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

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

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

--
Lazy AP

"Ken Johnson" a écrit dans le message de news:
...
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

You're welcome Ardus.

Ken Johnson

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" wrote:

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

Thanks for those kind words Ukyankee.

Ken Johnson

I'm glad I could help!

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

"Ukyankee" a écrit dans le message de
news: ...
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" wrote:

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 - 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
"Ardus Petus" wrote:

I'm glad I could help!

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

"Ukyankee" a écrit dans le message de
news: ...
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" wrote:

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

"Ukyankee" wrote:

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
"

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

"Ukyankee" a écrit dans le message de
news: ...
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

"Ken Johnson" wrote:

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

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:

"Ardus Petus" wrote:

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

"Ukyankee" a écrit dans le message de
news: ...
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 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:

"Ken Johnson" wrote:

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