Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Multi user | Excel Discussion (Misc queries) | |||
Multi-user | Excel Discussion (Misc queries) | |||
multi user | Excel Discussion (Misc queries) | |||
Multi user | Excel Discussion (Misc queries) | |||
Multi-User Access to a worksheet | Excel Programming |