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

 

[daffy]

Been following this post with interest
Just thought i would say thank you to VOG

Very cleaver and patient man

Thanks
Don

 

[ZEUSZEUS]

Peter's (VOG's) advice has been tremendous for me obviously. Feel indebted to him---just like you said Don, what a patient teacher.
Thanks again Peter.

-Tom in Toronto.

 

[daffy]

A couple of things i would ask are:
1, How do you stop the log from being opened? (if i password protect it i get an error message, when i start to add data to the other sheet)
2, Can a user be made to have to enable Macros to be able to open the sheet? (if macros are not enabled then the sheet wont open)
The reason i ask this is if the macros are not enabled then a user can delete whatever they want without trace.

VOG thanks for your help and support

Don

 

[VoG]

A couple of things i would ask are:
1, How do you stop the log from being opened? (if i password protect it i get an error message, when i start to add data to the other sheet)
2, Can a user be made to have to enable Macros to be able to open the sheet? (if macros are not enabled then the sheet wont open)
The reason i ask this is if the macros are not enabled then a user can delete whatever they want without trace.

VOG thanks for your help and support

Don

1. You can amend the code like this - change pw to the actual password:

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")
    .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
Application.EnableEvents = True
End Sub

2) See http://www.mrexcel.com/forum/showthread.php?t=255533 for an example of how to force users to enable macros.

 

[ZEUSZEUS]

Hi Peter,

We have been testing the code and excel sheet we created recently. We're close but still not there yet, (still want to get rid of any bugs that might arise).


1) Is there a way we can identify the present user (of the program), as opposed to who is logged in to windows? The reason is I might use the system to sign in a customer, then my employee might sign in a customer 5 minutes later. Logging off and on to windows between our uses would take too long.

Ideally for example, if there were say two passwords used on a "password protected" screensaver that would come on after 1 minute, and that password were recorded in the "log".
Maybe there is another way to do this?

Thanks,
Tom

 

[daffy]

Tom
Why not just add another column, for example column 6 where the person pust their details. I.e: Hair Dresser (name)

Even if they delete or edit it after you have gone or your not there for a while it will still be in the log as a change, an you can track it that way

Don

 

[ZEUSZEUS]

I thought about that Don, and you're right---simplest is best.

1) I was wondering is there a way to enter a common entry like "perm", "colour", or "styling" and common prices with dedicated button (key)?
-probably a very newb question, I know!

Thanks guys.
Tom

 

[VoG]

You can add a button from the Controls toolbar (View > Toolbars > Control), right click it, View Code and replace the top and tail that will probably be there with

Private Sub CommandButton1_Click()
ActiveCell.Value = "Perm"
ActiveCell.Offset(, 1).Value = 2.5
End Sub

which will write Perm in the current cell and the price one cell to the right. You can also change the name of the button to Perm.

 

[ZEUSZEUS]

I can't right click it, but I think it is the "control toolbox" that you are referring to (your use to 2007 maybe). I still don't understand it clearly....I see it has a "view code" that you can can click....and I assume I can write that code you sent me there.

1) Because of the variety of prices (each perm is usually different), I just need it to say perm, or Styling, or Colour, or highlights/cuts. I'll fill in the price.

2) Where is the button(s)? There is "command button", "option button", "text box"...I'm sorry it's probably so obvious I can't see it.

3) Ideally, can I program 5 or 6 buttons and name them?


thanks Peter.

 

[VoG]

I can't right click it, but I think it is the "control toolbox" that you are referring to (your use to 2007 maybe). I still don't understand it clearly....I see it has a "view code" that you can can click....and I assume I can write that code you sent me there.

1) Because of the variety of prices (each perm is usually different), I just need it to say perm, or Styling, or Colour, or highlights/cuts. I'll fill in the price.

2) Where is the button(s)? There is "command button", "option button", "text box"...I'm sorry it's probably so obvious I can't see it.

3) Ideally, can I program 5 or 6 buttons and name them?


thanks Peter.

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