Break/Lunch Log Sheet

[manoline]

Hello! I am new to the board and new to using VBA in Excel. Pardon me if I am not describing stuff well.

Anyway, I am currently working on building a break/lunch log sheet for my team that does not require my supervision. Currently, everyone has this log sheet printed and I have to sign and validate the start/end time of their break time, to identify those who are exceeding their given break schedule.

My requirement:
* They should only be allowed to enter their time stamp (both start and end) using a Command button.
* Manual manipulation should not be allowed (like entering the time stamp manually)
* Prevent edits on previous time stamp entries

What I have accomplished:
I have done the 1st 2 requirements. My issue is the 3rd one. I was able to prevent manual manipulation by locking all cells and make the command button click unlock the cells using the script. This is what I have currently.

Private Sub CommandButton1_Click()



Dim emptyRow As Long
ActiveSheet.Unprotect
ActiveCell = Now()


ActiveCell.Select
ActiveCell.Value = Time
Selection.Locked = True
Selection.FormulaHidden = False
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True




End Sub



Thanks in advance!

 

[Beyond_avarice]

Anyway, I am currently working on building a break/lunch log sheet for my team that does not require my supervision. Currently, everyone has this log sheet printed and I have to sign and validate the start/end time of their break time, to identify those who are exceeding their given break schedule.

As someone that has been a supervisor of employees and Payroll Director(For a large multinational non-profit. 2500 ee's under my watch); allow me to ruin your dream. Supervision of employees and their reporting of working hours will remain an essential responsibility for your position, regardless of what vehicle you utilize to track working hours. I had employees simply not punch in and instead make "manual edits". I had employees habitually "forget" to clock in and they "promise" that they were in and out at "this time"; it wasn't uncommon to catch a few not being honest about their claim.

After you have this "Scripted" and "Protected" timesheet; you still have the possibility of the above scenarios. One of which might require you to make manual entries on the employees behalf(read: more work for you). To help prevent some of these issues; your company should implement a "Disciplinary Procedure" that includes the "habitual failure" to utilize the time keeping system. Verbal warning, write up, suspension and termination.

Now that I have laid some real world pleasantries on you:

Private Sub CheckBox1_Click()

If CheckBox1.Value = True Then
    Sheets("Sheet3").Range("A1").Value = Time                   'Record time
    Sheets("Sheet3").Range("D1").Value = Date                   'Record Date
    Sheets("Sheet3").Range("G1").Value = Environ("Username")    'This will give the System Username
End If
End Sub

 

[manoline]

Thank you Beyond_avarice for taking time to respond. I appreciate it so much. Luckily, I'm already aware of the the things that employees could possibly do that will add more work to a supervisor. I've been there and that could possibly be one of my reasons too. Haha!

We do have a separate system that logs if employee is signing in and out of work however, what we lack is the break/lunch monitor. I could have left the Skype group chat messages to advice if they are going on break, however the bosses also needed their break usage report.

Anyway, let me try the script you provided and will give you feedback.

Again, thanks again for helping me. Have a nice day!

As someone that has been a supervisor of employees and Payroll Director(For a large multinational non-profit. 2500 ee's under my watch); allow me to ruin your dream. Supervision of employees and their reporting of working hours will remain an essential responsibility for your position, regardless of what vehicle you utilize to track working hours. I had employees simply not punch in and instead make "manual edits". I had employees habitually "forget" to clock in and they "promise" that they were in and out at "this time"; it wasn't uncommon to catch a few not being honest about their claim.

After you have this "Scripted" and "Protected" timesheet; you still have the possibility of the above scenarios. One of which might require you to make manual entries on the employees behalf(read: more work for you). To help prevent some of these issues; your company should implement a "Disciplinary Procedure" that includes the "habitual failure" to utilize the time keeping system. Verbal warning, write up, suspension and termination.

Now that I have laid some real world pleasantries on you:

Private Sub CheckBox1_Click()

If CheckBox1.Value = True Then
    Sheets("Sheet3").Range("A1").Value = Time                   'Record time
    Sheets("Sheet3").Range("D1").Value = Date                   'Record Date
    Sheets("Sheet3").Range("G1").Value = Environ("Username")    'This will give the System Username
End If
End Sub

 

[Beyond_avarice]

Hey us Supervisors know the pain. I've thought more about how I could help and have come up with the following:

1. Utilize the above script to populate the Sheet that will keep track of the inputs. Allow them to make edits.
2. Include the following script within the "Worksheet" that you will collect data from. Every activity will be recorded to protected "Audit" Sheet as a means of audit. It will put your password through to open process and then protect again after completion.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim auditRow As Long

auditRow = Worksheets("Audit").Cells(Rows.Count, 1).End(xlUp).Offset(1).Row     'Identify last used row for appending of next audit record

     If Not Intersect(Target, Target.Worksheet.Range("A3:H10")) Is Nothing Then 'Set your range that you will allow to be manipulated
    
        
    Worksheets("Audit").Unprotect ("insert Password")
    
    
            With Sheets("Audit")
               .Range("A" & auditRow).Value = Target
               .Range("B" & auditRow).Value = Target.Address
               .Range("C" & auditRow).Value = Time
               .Range("D" & auditRow).Value = Date
               .Range("E" & auditRow).Value = Environ("Username")
                             
            End With
     End If
 
    Worksheets("Audit").Protect ("insert Password")
End Sub

This will require you to close the "Microsoft Objects" folder and password protect it, else your password will be revealed and EE's can just unprotect the audit page and make edits. Part of the pain of this will be the contents of Column "A". You will need to interpret what is a date and what is a time. If an EE knows and can see a Sheet called "Audit" that is recording all of their activities; they will be "more likely" to be honest.