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

 

[VoG]

To prevent the logging code from running, code your button code like this

Private Sub Button1_Click()
Application.EnableEvents = False
'
'your code here
'
Application.EnableEvents = True
End Sub

To add code programatically see http://www.cpearson.com/excel/VBE.aspx

 

[Spennny]

First of all i would like to say what a fantastic site , and i have used the below macro which has helped me alot,

I have one question on the below, when i enter something into the cell and then try to go down, the cell to the right gets highlighted ,

Is there something in the code that i can amend so when i want to go to the cell directly under what i have typed in,


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

 

[VoG]

Just delete

Target.Offset(, 1).Select

 

[Spennny]

Brilliant, Thankyou very much for the quick response

 

[ahs004]

Hi Peter,

Just a quick question.

This is exactly the code i have been looking for and here you have it.

I am not sure if i am having a dumb day but i can not get the code to work.

I am using Version 2003. Created a worksheet called "log" and copied the code into that worksheets coding, no luck.

I have cpoied te code in as a module and to other sheets but no luck either.

Can you help?

Many thanks.

 

[VoG]

The code has to go in the ThisWorkbook module. Right click the Excel logo just to the left of File on the menu bar, select View Code and paste in the code.

 

[ahs004]

Wow, short but powerful coding.

You are amazing Peter, Many Thanks.

 

[Siyanna]

Hi Vog, this is awesome.

What a leg. I have had the chance to read it properly but seems like this is what i need.

I think 1 part of the code which will interest me is that if the environ(username) is not myself then exit sub so only i can run the macro.

Is there a way where like this, the 4 guys who have access to this spreadsheet are only allowed to view the spreadsheet and not able to run the code and do anything to the spreadsheet? like read only but they are not aloud to save, or save as or do anything to the spreadsheet other than read/only.

To be able to run the macro or save any changes or saveas, i need the user to be able to enter password however they will need to askme for that password or something, i.e i receive an email with a random password and for them to log run the code or do anything they will need that password. That way each time something is done they will need to go through me and then i will know that any changes made is by the person who took the password of me.

Each time the workbook is saved and closed, when reopened the password can be different and then they will have to do the same process.

This way i will not need to know the changes but only what time they logged in and logged out.

Hope this can be done with Excel VBA

Thank YOu