Staff Overtime Form

Dear experts,

I've a worksheet recording staffs' overtime status. Each row is for one day.
For example, a staff input start time and end time in cells A2 and B2. The
overtime hours will be shown in C2. D2 is for overtime description. The
question is, in cell E2, the supervisor will type either 'approve' or 'eject'
into the cell. After he typed the word, I want the whole record or row 2 will
be locked and cannot be edited by anyone except the supervisor by entering a
password, say"reopen". This method should be applied to every row as the
staff needs to input the overtime details day by day. Can it be done by a
macro code? If yes, what will it be? Please kindly advise.

Thanks in advance.

Hi

Here's a solution

As the worksheet has to be protected to lock the cells, you have to
unlock *all* input cells in the sheet first. Also you need a
CommandButton on the sheet to unlock selected row.

This is event code, so it has to be pasted into the code sheet for the
desired worksheet!

Const pWord As String = "JustMe"
Private Sub CommandButton1_Click()
Dim Hil As String

Hil = "Best regards, Jessen"
If Selection.Rows.Count 1 Then Exit Sub
rw = ActiveCell.Row
msg = MsgBox("Do you want to unlock cells A" & rw & ":E" & rw & " ?",
vbQuestion + vbYesNo, Hil)
If msg = vbYes Then
Answer = InputBox("Enter password to unlock cells :", Hil)
If Answer = "Admin" Then 'Change to suit
ActiveSheet.Unprotect password:=pWord
Range("A" & rw & ":E" & rw).Locked = False
ActiveSheet.Protect password:=pWord
Application.EnableEvents = False
Range("E" & rw).ClearContents
Application.EnableEvents = True
End If
End If
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
Set isect = Intersect(Target, Columns("E"))
If Not isect Is Nothing Then
If LCase(Target.Value) = "approve" Or LCase(Target.Value) =
"eject" Then
ActiveSheet.Unprotect password:=pWord
Target.Offset(0, -4).Resize(1, 5).Locked = True
ActiveSheet.Protect password:=pWord
msg = MsgBox("Data has been locked !", vbOKOnly +
vbExclamation, "Regards, Jessen")
End If
End If
End Sub

Regards,
Per

On 22 Mar., 10:35, Freshman
wrote:
Dear experts,

I've a worksheet recording staffs' overtime status. Each row is for one day.
For example, a staff input start time and end time in cells A2 and B2. The
overtime hours will be shown in C2. D2 is for overtime description. The
question is, in cell E2, the supervisor will type either 'approve' or 'eject'
into the cell. After he typed the word, I want the whole record or row 2 will
be locked and cannot be edited by anyone except the supervisor by entering a
password, say"reopen". This method should be applied to every row as the
staff needs to input the overtime details day by day. Can it be done by a
macro code? If yes, what will it be? Please kindly advise.

Thanks in advance.

Hi Per,

Thanks for your help. It works very well.

"Per Jessen" wrote:

Hi

Here's a solution

As the worksheet has to be protected to lock the cells, you have to
unlock *all* input cells in the sheet first. Also you need a
CommandButton on the sheet to unlock selected row.

This is event code, so it has to be pasted into the code sheet for the
desired worksheet!

Const pWord As String = "JustMe"
Private Sub CommandButton1_Click()
Dim Hil As String

Hil = "Best regards, Jessen"
If Selection.Rows.Count 1 Then Exit Sub
rw = ActiveCell.Row
msg = MsgBox("Do you want to unlock cells A" & rw & ":E" & rw & " ?",
vbQuestion + vbYesNo, Hil)
If msg = vbYes Then
Answer = InputBox("Enter password to unlock cells :", Hil)
If Answer = "Admin" Then 'Change to suit
ActiveSheet.Unprotect password:=pWord
Range("A" & rw & ":E" & rw).Locked = False
ActiveSheet.Protect password:=pWord
Application.EnableEvents = False
Range("E" & rw).ClearContents
Application.EnableEvents = True
End If
End If
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
Set isect = Intersect(Target, Columns("E"))
If Not isect Is Nothing Then
If LCase(Target.Value) = "approve" Or LCase(Target.Value) =
"eject" Then
ActiveSheet.Unprotect password:=pWord
Target.Offset(0, -4).Resize(1, 5).Locked = True
ActiveSheet.Protect password:=pWord
msg = MsgBox("Data has been locked !", vbOKOnly +
vbExclamation, "Regards, Jessen")
End If
End If
End Sub

Regards,
Per

On 22 Mar., 10:35, Freshman
wrote:
Dear experts,

I've a worksheet recording staffs' overtime status. Each row is for one day.
For example, a staff input start time and end time in cells A2 and B2. The
overtime hours will be shown in C2. D2 is for overtime description. The
question is, in cell E2, the supervisor will type either 'approve' or 'eject'
into the cell. After he typed the word, I want the whole record or row 2 will
be locked and cannot be edited by anyone except the supervisor by entering a
password, say"reopen". This method should be applied to every row as the
staff needs to input the overtime details day by day. Can it be done by a
macro code? If yes, what will it be? Please kindly advise.

Thanks in advance.

.