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]

Also, when I "Protect the Workbook" it makes the sheets smaller in size. All of my sheet info was easily seen on one page before, now I have to scroll to one side and the other. Is there a way to increase this size?

 

[ZEUSZEUS]

...UPDATE-- I "protected windows" instead of just structure so I think that's the reason.

 

[VoG]

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)

Try this: add this to the code that you already have

Private Sub Workbook_BeforeClose(Cancel As Boolean)
ThisWorkbook.Save
End Sub

 

[ZEUSZEUS]

Is there a way to stop workbook being saved with another name & path? (To prevent users from saving info to their own disks, usb storage etc.)

The save on closing skips this step (excellent!!, thanks btw), but they can still "save as" and pick a route.

Tom

 

[VoG]

Try

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

 

[ZEUSZEUS]

Hi Peter,

I was doing trials and came across a problem if I delete an entire line. (I remember it working before we added the "what was previous value column". The error coming up has to do with code line
"Target.Offset (, 1).Select"
(not 100% sure, don't have laptop here today)
I assume that it might not know how to indicate the values in the whole line erased (would be infinite(?) maybe?). Would be great if it could...for us, we are using up to column "T".

thanks
-tom.

 

[VoG]

Try this

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
End Sub

 

[ZEUSZEUS]

Good evening Peter,

You solved the problem of automatically saving on closing (without prompting), I came across another related one. What if rather than close, you "shut down" or "log off"...when I do these now it prompts me to save my work. Can it auto save and bypass prompts in these instances?

Thanks
Tom in Toronto

 

[ZEUSZEUS]

FYI, here's the code together so far. (Thanks to Peter).

<link rel="File-List" href="file:///C:%5CDOCUME%7E1%5CTHOMAS%7E1%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml"><!--[if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:Compatibility> <w:BreakWrappedTables/> <w:SnapToGridInCell/> <w:WrapTextWithPunct/> <w:UseAsianBreakRules/> </w:Compatibility> <w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel> </w:WordDocument> </xml><![endif]--><style> <!-- /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-parent:""; margin:0in; margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:12.0pt; font-family:"Times New Roman"; mso-fareast-font-family:"Times New Roman"; mso-ansi-language:EN-CA; mso-fareast-language:EN-CA;} @page Section1 {size:8.5in 11.0in; margin:1.0in 1.25in 1.0in 1.25in; mso-header-margin:35.4pt; mso-footer-margin:35.4pt; mso-paper-source:0;} div.Section1 {page:Section1;} --> </style><!--[if gte mso 10]> <style> /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-parent:""; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-para-margin:0in; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman";} </style> <![endif]--> 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
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
ThisWorkbook.Save
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

 

[VoG]

Hi

I haven't tested this but try replacing the BeforeClose code with this

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.DisplayAlerts = False
ThisWorkbook.Save
Application.DisplayAlerts = True
End Sub