Tracking my employee's history of changes.

[ZEUSZEUS]

Have a dilemma.
I am using excel as point of sale book (to record customer name, service, and total price etc.) at our hair salon. We have employees that may be there to manage alone from time to time, and enter clients into excel.
Our worry is straight forward, and involves them erasing what they wrote. I am confident that the actual service and price is entered at the beginning, but want to track their changes to their own entries.

The "track changes" would work if it "tracked changes" after entry. But it seems to track the last change from saving. For example....the employee enters $40.25 presses enter--after she knows she can get away with a change, she may erase it altogether or change it to say $16.75.

Please help.
Thanks in advance

 

[ZEUSZEUS]

As this has now moved onto a completely new topic can I suggest that you start a new thread for it

Agreed.

If I have further related questions I'll post here.

Thanks so much Peter.

 

[ZEUSZEUS]

Peter, when I did the "protect sheet password" with code you added for Don and I, it worked great. The only thing is the cursor (or box around the new cell) disappears now. For example, if you start on A1 as you hit enter (the screen flashes for the quickest instant) and then the cursor is not on any cell. When you begin to write the next entry anyways it is indeed there in A2 though (I have cells 'move right after enter").

I checked when I tried to see what it looked like as before the "protect sheet password code" with the original code and it appears fine there.

Is there any way to get it back?

 

[VoG]

Try this

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim LR As Long
If Sh.Name = "Log" Then Exit Sub
With Application
    .EnableEvents = False
    .ScreenUpdating = False
End With
With Sheets("Log")
    .Unprotect password:="pw"
    LR = .Range("A" & Rows.Count).End(xlUp).Row
    .Range("A" & LR + 1).Value = Now
    .Range("B" & LR + 1).Value = Sh.Name
    .Range("C" & LR + 1).NumberFormat = "@"
    .Range("C" & LR + 1).Value = Target.Address(False, False)
    .Range("D" & LR + 1).Value = Target.Value
    .Range("E" & LR + 1).Value = Environ("username")
    .Protect password:="pw"
End With
Target.Offset(, 1).Select
With Application
    .EnableEvents = True
    .ScreenUpdating = True
End With
End Sub

 

[Aleciafxc]

Great Post, is there a way to also add the previous value to the Log Sheet?? I have found much about adding a comment into the changed cell, that adds the preValue, but that become combersom. Also i need the log to log for every non protected cell on about 6 sheets. Any assistance would be greatly appreciated.

Thanks Alecia

 

[VoG]

Great Post, is there a way to also add the previous value to the Log Sheet?? I have found much about adding a comment into the changed cell, that adds the preValue, but that become combersom. Also i need the log to log for every non protected cell on about 6 sheets. Any assistance would be greatly appreciated.

Thanks Alecia

This will put the old value in column D and the new value in E.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim LR As Long, X As Variant
If Sh.Name = "Log" Then Exit Sub
X = Target.Value
With Application
    .EnableEvents = False
    .ScreenUpdating = False
    .Undo
End With
With Sheets("Log")
    .Unprotect Password:="pw"
    LR = .Range("A" & Rows.Count).End(xlUp).Row
    .Range("A" & LR + 1).Value = Now
    .Range("B" & LR + 1).Value = Sh.Name
    .Range("C" & LR + 1).NumberFormat = "@"
    .Range("C" & LR + 1).Value = Target.Address(False, False)
    .Range("D" & LR + 1).Value = Target.Value
    .Range("E" & LR + 1).Value = X
    .Range("F" & LR + 1).Value = Environ("username")
    .Protect Password:="pw"
End With
Target.Value = X
Target.Offset(, 1).Select
With Application
    .EnableEvents = True
    .ScreenUpdating = True
End With
End Sub

 

[Aleciafxc]

You Rock, why didn't it need "preValue"?? Just curious. Most of what I read said I needed to define a preValue. Anyway this is great. Thank you so much.

Al

 

[VoG]

I've commented the code to show how the old value is obtained:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim LR As Long, X As Variant
If Sh.Name = "Log" Then Exit Sub
X = Target.Value '<<<<< save the new value
With Application
    .EnableEvents = False
    .ScreenUpdating = False
    .Undo '<<<<< restore the previous value
End With
With Sheets("Log")
    .Unprotect Password:="pw"
    LR = .Range("A" & Rows.Count).End(xlUp).Row
    .Range("A" & LR + 1).Value = Now
    .Range("B" & LR + 1).Value = Sh.Name
    .Range("C" & LR + 1).NumberFormat = "@"
    .Range("C" & LR + 1).Value = Target.Address(False, False)
    .Range("D" & LR + 1).Value = Target.Value '<<<<< old value
    .Range("E" & LR + 1).Value = X '<<<<< new value
    .Range("F" & LR + 1).Value = Environ("username")
    .Protect Password:="pw"
End With
Target.Value = X '<<<<< restore the new value
Target.Offset(, 1).Select
With Application
    .EnableEvents = True
    .ScreenUpdating = True
End With
End Sub

 

[Aleciafxc]

Thanks, makes sense to me. You should post this in the MVP section. I am new to the code but not excel. I appreciate your assistance and my staff will appreciate the backup log.

 

[ZEUSZEUS]

Excellent question Alecia,
and excellent answer Peter.
ray:
Tom in Toronto

 

[ZEUSZEUS]

Peter,

How do I automatically save the workbook on closing without being prompted?
(To make sure the other users don't actually "forget to save" the day's work)

Tom

PS- Everything is working like a charm, so far. I'm appreciating Excel more than I ever have, especially with your help on codes and the like!