Checking for changes on a multi-user worksheet
 

I have a spreadsheet in which several people enter data throughout the day.
Occasionally, a user will change another user's numbers. This is NOT good,
and I need a way to track which data has been altered. I am tracking users
by their Windows login (variable is uName), and entering their initials in
column P. Each user has a unique cell color, which is also entered in column
P.

I'm having a tough time figuring out the next step though. Locking cells
would be counter productive. I would like to insert some worksheet code
which would immediately detect if a user has changed a previous user's data,
by coloring the changed data cell with the current user's unique color.

Example:
User #1 enters 5 in cell D2
User#2 changes the 5 in cell D2 to a 3
worksheet routine highlights cell D2 with User#2's color, but doesn't block
the change.

Any help would be appreciated.

McBarker

Checking for changes on a multi-user worksheet
 

his is from the JWalk site, and might be helpful to you.

by David Hager

The following event procedures work together to place the contents of a cell
into a cell comment when another entry is made. For example, if a cell
contains a value of 13, and 23 is entered in the cell, the cell comment will
contain the statement:

"Previous entry was 13"

Public acVal

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error Resume Next
Target.AddComment
Target.Comment.Text "Previous entry was " & acVal
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If ActiveCell.Address < Target.Address Then Exit Sub
If Target.Value = "" Then
acVal = ""
Else
acVal = Target.Value
End If
End Sub

The cell value is stored in a public variable when a cell is selected. Then,
when a new value is added, the Worksheet_Change event procedure adds a cell
comment (the error generated if the cell already has a comment is stepped
over) and then uses the stored variable as part of the text string for the
comment. This technique could be easily modified to add all of the changes
made to a cell over time to the comment.

--
Alan Hutchins


"McBarker" wrote:

I have a spreadsheet in which several people enter data throughout the day.
Occasionally, a user will change another user's numbers. This is NOT good,
and I need a way to track which data has been altered. I am tracking users
by their Windows login (variable is uName), and entering their initials in
column P. Each user has a unique cell color, which is also entered in column
P.

I'm having a tough time figuring out the next step though. Locking cells
would be counter productive. I would like to insert some worksheet code
which would immediately detect if a user has changed a previous user's data,
by coloring the changed data cell with the current user's unique color.

Example:
User #1 enters 5 in cell D2
User#2 changes the 5 in cell D2 to a 3
worksheet routine highlights cell D2 with User#2's color, but doesn't block
the change.

Any help would be appreciated.

McBarker

Checking for changes on a multi-user worksheet
 

Alan, thanks for this. It looks promising. I'll try modifying it to fit in
the next couple of days.

Alan Hutchins wrote:
his is from the JWalk site, and might be helpful to you.

by David Hager

The following event procedures work together to place the contents of
a cell into a cell comment when another entry is made. For example,
if a cell contains a value of 13, and 23 is entered in the cell, the
cell comment will contain the statement:

"Previous entry was 13"

Public acVal

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error Resume Next
Target.AddComment
Target.Comment.Text "Previous entry was " & acVal
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If ActiveCell.Address < Target.Address Then Exit Sub
If Target.Value = "" Then
acVal = ""
Else
acVal = Target.Value
End If
End Sub

The cell value is stored in a public variable when a cell is
selected. Then, when a new value is added, the Worksheet_Change event
procedure adds a cell comment (the error generated if the cell
already has a comment is stepped over) and then uses the stored
variable as part of the text string for the comment. This technique
could be easily modified to add all of the changes made to a cell
over time to the comment.


I have a spreadsheet in which several people enter data throughout
the day. Occasionally, a user will change another user's numbers.
This is NOT good, and I need a way to track which data has been
altered. I am tracking users by their Windows login (variable is
uName), and entering their initials in column P. Each user has a
unique cell color, which is also entered in column P.

I'm having a tough time figuring out the next step though. Locking
cells would be counter productive. I would like to insert some
worksheet code which would immediately detect if a user has changed
a previous user's data, by coloring the changed data cell with the
current user's unique color.

Example:
User #1 enters 5 in cell D2
User#2 changes the 5 in cell D2 to a 3
worksheet routine highlights cell D2 with User#2's color, but
doesn't block the change.

Any help would be appreciated.

McBarker