Annual leave tracker

estelle

New Member
Joined
Jun 12, 2018
Messages
10
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?
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
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.
 
Upvote 0
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.
Code:
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,537
Messages
6,114,216
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top