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]

Forgot to mention that the laptop I bought for the salon has Vista. Can I use Excel versions 10 or 11 on Vista?

 

[VoG]

Can I use Excel versions 10 or 11 on Vista?

Yes, as far as I know.

 

[ZEUSZEUS]

Hi Peter,
Wow, the code "worked a treat!" as you Brits would say. I am very impressed now that we tried a little test on it tonight and we see it in action. Thank you very much for this help! My wife and I are going to try some more testing tomorrow.

1) I did notice though that when we erased a line (ie. right click on line 4 for example and delete) it can't indicate that. What it does is it writes a time format for the "Cell Address" ie. 4:04 (also when highlighted it means "4:04:00 AM" in the function line)--no clue why though. Is there a way to indicate this deletion of a line, and/or is there a way to eliminate deletion of lines altogether?

2)Is there a way to have different loggers between myself and each worker (that expires after 30seconds to a minute)? . Can the "Log" indicate the loggers too?

Great stuff!
-Tom in Toronto

 

[VoG]

The row display was just a formatting thing. With the amended code below deleting row 4 will display 4:4 and deleting column B will display B:B. I've added column E to contain the user's Windows logon name so that provided you switch users it will record who made the change:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim LR As Long
If Sh.Name = "Log" Then Exit Sub
Application.EnableEvents = False
With Sheets("Log")
    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")
End With
Application.EnableEvents = True
End Sub

 

[ZEUSZEUS]

Hi Peter,

1) I made the changes. This morning and can't get the log to record new tests. A warning popped up saying
"Macros are disabled because security level is set to High and a digitally signed Trusted Certificate is not attached to the macros....." is this affecting it, what do you recommend?

2) When I close "view code" is it already saved and running in the background of the workbook (it doesn't have a save button)?

 

[VoG]

1) Go to Tools > Macros > Security and lower the security setting.

2) As soon as you change the code the new version comes into effect. However, it would be sensible to save the workbook after changing the code.

 

[ZEUSZEUS]

1) Is there a quick way to auto log-off users so as to log-in quick to the open sheet (just so "log" indicates who is using).

2) When entering a customer into the line, we would like to enter it across a line from A to F. Is there a way to move the cursor sideways by pressing "enter" (without "tabbing"), or is it something we must get used to?

BTW, The code is working really well. I tried deleting two and three lines at a time from line 3 for example, and it shows me 3:4 or 3:5. So far this "code" allows for an awesome account of what is happening. Very pleased.
thank you
-Tom in Toronto

PS- I put in excel 2003 on my vista laptop to use with my excel 2002 Xp interchangeably (without a pack). I am aiming to bring this to work for Thursday!

 

[VoG]

1) You can use Fast User Switching in Windows. See Windows Help.

2) I *think* (because I'm using 2007) that in 2003 it is Tools > Options > Edit tab, Sfter Pressing Enter move selection and pick Right.

 

[ZEUSZEUS]

Can I prevent a sheet (or an entire workbook) from being deleted "by accident" by a employee?

 

[VoG]

To prevent a sheet being deleted Tools > Protection > Protect Workbook, tick Structure, apply a password and click OK.