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]

Hi Peter,
I tested it after the new change, and it still works to save on close. However, it still prompts to save or not when I either "log off" or "shut down".
Hope to hear from you tomorrow.
Good night.
-Tom in Toronto

 

[VoG]

Try adding the line in red. If this causes Excel to become slow, delete it.

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
    If Target.Count = 1 Then .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
If Target.Count = 1 Then Target.Value = X
On Error Resume Next
Target.Offset(, 1).Select
On Error GoTo 0
With Application
    .EnableEvents = True
    .ScreenUpdating = True
End With
ThisWorkbook.Save
End Sub

 

[ZEUSZEUS]

Before I test, do I keep the

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.DisplayAlerts = False
ThisWorkbook.Save
Application.DisplayAlerts = True
End Sub
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If SaveAsUI Then
MsgBox "Save As prohibited", vbExclamation
Cancel = True
End If
End Sub

****the last try to correct for the "log off/shut down auto save" issue correction is in red. I want to eliminate anything that might affect the test of the new code.

 

[VoG]

Yes, keep everything else as is.

 

[ZEUSZEUS]

Yes Peter, you're suspicion was correct, the system now stalls.
I think that you have it saving after each entry which seems a great idea, but my computer can't do it fast enough.
Your "save on closing" code worked perfectly, seemed so easy too. Still prompted on "logging off / shutting down" would seem like a similar fix?

Your an excel'lent help!
-tom

 

[VoG]

You might want to post a new thread about this latest issue.

I think that it will require trapping a Windows event (switch user or shut down) and I don't know how to do that.

 

[ZEUSZEUS]

Hi Peter,
I haven't had much luck with responses to the force save on shut-down or log-off. I've bumped and re-posted with no response.

I was wondering (taking from your last idea that essentially "saved" after every cell-entry) if it was possible to "save" only for a specific column. For example if it saved after the price column cell's entry. This column has the most important values (worth changing to a cheating employee) anyways.

Tom
PS- thanks for your responses to date, especially considering my luck with my other posts responses-lol

 

[Ed in Aus]

Have to say Peter this thread is great I just want a situation where I can use it...

What if you had an auto save sequence that ran every five mins? storing when it was last saved in the log too?

 

[VoG]

What if you had an auto save sequence that ran every five mins? storing when it was last saved in the log too?

You could try modifying the BeforeSave code to

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If SaveAsUI Then
    MsgBox "Save As prohibited", vbExclamation
    Cancel = True
Else
    With Sheets("Log")
        .Unprotect Password:="pw"
        LR = .Range("A" & Rows.Count).End(xlUp).Row
        .Range("A" & LR + 1).Value = Now
        .Range("G" & LR + 1).Value = "Saved"
        .Protect Password:="pw"
    End With
End If
End Sub

 

[ZEUSZEUS]

To clarify, is this meant to save on a certain column? So in your example, is it column "G" that initiates saving?
If this is it, then this satisfies the saving issue for me.

(It's just that you quoted Ed's "auto save sequence")

-Tom