Annual leave tracker

[estelle]

I have created an annual leave tracker for use at work. My problem is that although you can pick from a dropdown A/L pending, A/L Approved and A/L denied, with conditional rules to change colours, at the moment anyone with access can at the moment do all three. Is there a way to ensure that only the manager can approve or deny. I know how to add and track user logins for anyone who I want to grant access to the file but not how to limit which conditional rules or dropdowns they can use. Can it be done?

 

[skoorBmaS]

If you select all cells and then press CTRL+1, a pop up box should appear. Under Protection, uncheck locked cells. Now go to the cells you want only the manager to approve and highlight those and repeat the last step, however this time checking locked. Now go to file and then protect this sheet. Add a password and now only those with that password can change those cells.

 

[mumps]

Assuming the dropdown is in cell A1, copy and paste this macro into the worksheet code module. Do the following: right click the tab for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Change the target range in the code to suit your needs. Replace "manager username here" with the manager's username. Close the code window to return to your sheet. Make a selection in the dropdown.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("A1")) Is Nothing Then Exit Sub
    Select Case Target.Value
        Case "A/L Approved", "A/L denied"
            If Application.UserName <> "manager username here" Then
                Target.ClearContents
                MsgBox ("Only the Manager can approve or deny.")
                Exit Sub
            End If
    End Select
End Sub

 

[estelle]

Thank you so much. This is exactly what I was looking for. Perfect ?

 

[estelle]

This would work but would have to in essence have two cells for each cell, one to request and another to approve. But many thanks for quick reply.

 

[mumps]

Could you explain in detail what you mean by

would have to in essence have two cells for each cell, one to request and another to approve

 

[estelle]

Could you explain in detail what you mean by

Sorry Mumps, that comment was meant for the person who very kindly suggested the other solution. Your code in fact is exactly what I was looking for and will work perfectly. Thank you so much and my apologies for my not being clearer.

 

[mumps]

You are very welcome.