Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Staff Overtime Form
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Staff Overtime Form
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. . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Overtime Calculation for Overtime | Excel Worksheet Functions | |||
=SUMPRODUCT((Overtime!$J$6:$GY$6-DAY(Overtime!$J$6:$GY$6)+1=A25)*(Overtime!$J$7:$GY $2 | Excel Worksheet Functions | |||
new staff | Excel Worksheet Functions | |||
Staff Rosters. | Excel Discussion (Misc queries) | |||
how many staff have 1 skill, how many staff have 2 skills, etc. | Excel Discussion (Misc queries) |