Suggestions Needed to Protect a Sheet within a Workbook

[spectraflame]

At one of our facilities they desire to use Excel as an electronic version of a Log Book. The idea is instead of hand writing everything in a ledger, they would enter it into a spreadsheet. This would give us the ability to sort and report on entries based on date, day of week, key words, category, or employee. A concern has been raised about ensuring that Employee A's entries are not modified by Employee B. This sheet will be used by a 24 hour rotating crew. Each person is using a restricted network account to gain access to the spreadsheet. The only way we are denoting who created each entry is from a drop down list on each row.

First question would be if this is advisable? Would it be best to use some type of macro triggered by the Wookbook close event? I realize the employee is going to have to be diligent enough to enable the Macro when the sheet is opened each time. Also if Employee A wanted to create a false entry and label it as Employee B, this could be done as well. Should I consider prompting for a name and unique password to open the shared spreadsheet and then limit the employee name to whomever opens the sheet? Should I use some type of entry form that would allow users to make entries and then make that sheet read only? Am I making this too complicated?

I would appreciate any input on thoughts on doing this or examples you have used in the past for something similar.

 

[Logit]

.
I would recommend creating a workbook that has the following :

Main log in sheet. When the workbook opens it auto-opens to the login sheet.

A userform is auto presented. The form is a basic log-in form allowing for UserName and Password only.

When the appropriate Username and Password is entered, the sheet for that employee (and that employee only) is opened.
The employee will not have access to any other sheets in the workbook.

Each employee will have their own sheet to enter their shift's data.

Have the employee log off at the end of their shift, which closes the workbook and autosaves everything.

The oncoming employee opens the workbook and the process begins again.


For report purposes, you could make an Administrators sheet that enables you to copy data entries from all employee shifts. From that
information you can run your reports.

Here is an example of password protecting each sheet. You can expand from there : https://www.amazon.com/clouddrive/share/mYbTdQkxlOfY6T6Q793Y7JoewDnLKvYtCHG4DGMACqJ

 

[spectraflame]

That is pretty neat how you have the user authentication. If I could pass that authenticated username to a cell and use it as the only option the Employee has of denoting who made the entry, I could use a simple Change event like this. i would just have to make sure I protected the VB code from viewing.

Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.Unprotect Password:="password"
Target.Locked = True
ActiveSheet.Protect Password:="password"
End Sub