Lock cell if row already has "V" as a value

[jessicarabelli]

Hello,

I have created a corporate calendar where the rows contain the days of the month and the columns contain the employee's name (14 employees total) and the cells may contain V for vacation when the employee will be out of the office. I do not want more than one employee to take vacation at the same time.


I would like to block a cell if the value "V" has already been entered in that specific day. How can I do that?

PS: I am creating this calendar per the request of my manager, not my idea to block employees from taking vacation on the same day.

 

[jessicarabelli]

So If employee 7 can not take vacation if anyone has already requested vacation?

Employee 7 can take vacation whenever he wants, he has no constrains. But when he is on vacation, no one else can take vacation. Employee 7 is actually the manager...HA

 

[Scott T]

I am sure this could be coded better but it seems to work

Right click on the sheet tab and select View Code
past the code below
the spreadsheet must be save as a macro enabled file type for example .xlsm

Not sure what should happen if the manager wants to take vacation and another employee already requested vacation so just has a message box noting that someone has requested vacation.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If UCase(Target) <> "V" Then Exit Sub
trow = Target.Row
tcol = Target.Column
Application.EnableEvents = False
If UCase(Cells(2, tcol)) = "EVERYONE" Then
    If Application.WorksheetFunction.CountIf(Range("B" & trow & ":P" & trow), "V") > 1 Then
        MsgBox ("another employee has requested vacation must resolve")
        Application.EnableEvents = True
        Exit Sub
    End If
Else
    depcol = Application.Match(Cells(2, tcol), Range("B1:P1"), 0) + 1
    If UCase(Cells(trow, depcol)) = "V" Then
        MsgBox ("cannot take vacation")
        Target.ClearContents
        Application.EnableEvents = True
        Exit Sub
    End If
For x = 2 To 16
    If UCase(Cells(2, x)) = "EVERYONE" And UCase(Cells(trow, x)) = "V" Then
        MsgBox "Cannot take vacation"
        Target.ClearContents
        Application.EnableEvents = True
        Exit Sub
            
    End If
Next x
    
For i = 2 To 16
    If Cells(2, i) = Cells(1, tcol) And UCase(Cells(trow, i)) = "V" Then
        MsgBox "Cannot take vacation"
        Target.ClearContents
        Application.EnableEvents = True
    End If
Next i

End If
Application.EnableEvents = True

End Sub

 

[Rick Rothstein]

Employee 7 can take vacation whenever he wants, he has no constrains. But when he is on vacation, no one else can take vacation. Employee 7 is actually the manager...HA

But what if someone else already signed up for a vacation on a certain day and the manager decided later that he wanted that day off... does the manager evict that other employee from his already signed up for day? In other words, using the table you posted in Message #5 , what happens if Emp7 wants day 3 off... does he get to put a "v" on the day's row? And, if so, does the "v" for Emp2 get deleted? Whatever your answer is for that question, does it apply to any employee/supervisor combination?

 

[jessicarabelli]

But what if someone else already signed up for a vacation on a certain day and the manager decided later that he wanted that day off... does the manager evict that other employee from his already signed up for day? In other words, using the table you posted in Message #5 , what happens if Emp7 wants day 3 off... does he get to put a "v" on the day's row? And, if so, does the "v" for Emp2 get deleted? Whatever your answer is for that question, does it apply to any employee/supervisor combination?

Rick, no "v" should be deleted - independent of who enters it (could we get a rule that locks a cell after someone enters a value in it so that someone else cannot just go there and delete the value to enter their "v"?).

Whomever claims the "v" first for that specific date gets the vacation day. I mean, this can be overwritten by the manager later, but for now I just need to figure out the formula to block the date for employee/deputy combination).

 

[Rick Rothstein]

Rick, no "v" should be deleted - independent of who enters it (could we get a rule that locks a cell after someone enters a value in it so that someone else cannot just go there and delete the value to enter their "v"?).

Whomever claims the "v" first for that specific date gets the vacation day. I mean, this can be overwritten by the manager later, but for now I just need to figure out the formula to block the date for employee/deputy combination).

So if the employee takes a day, the deputy have that day and, likewise, if the deputy takes a day, the employee cannot have that day, correct?

As for the manager, it is easier to incorporate his/her "powers" while developing the code as opposed to developing the code without considering his/her powers and then trying to shoe-horn them into the code later on. So, what happens to all the V's already checked off on the table if the manager decides he/she wants that day off?

If the day is clear (no V's) and the manager places a V on that day... does that mean no one else can place a V for that day? Or is there a list of people so low on the totem pole that their absence on the same day as the manager's does not matter (hence they could take off on the same day)?

 

[jessicarabelli]

Rick, employees and deputies work side by side on a daily basis. But if the employee takes the day off, WE MUST have the deputy on site to cover the employee as well as deputy's work - otherwise we will have no one to help us. If the deputy takes the day off, any employee that is either HIS/HER deputy OR "supervisor" cannot take the day off. (ie: if employee 13 requests vacation, employee 12 and 15 cannot take vacation on that day since #13 is deputy to 12 and 15).

To override this I can just select the cell I want to clear the validation and select "clear validation" so that will not be a problem if the manager decided to allow both employee and supervisor go on vacation...

 

[Rick Rothstein]

To override this I can just select the cell I want to clear the validation and select "clear validation" so that will not be a problem if the manager decided to allow both employee and supervisor go on vacation...

Well, it would be a problem if I give you the code I have in mind. What I planned to do is what Scott T did... give you a Worksheet_Change event procedure that would run automatically to block the placing of a duplicate V when it should not be placed. As an automatic procedure, you would not have the ability to override it unless I build that capability into the code. That is what I meant about not wanting to try and shoehorn in additional privileges afterwards. So maybe you need to give us a little more detail about how the code is supposed to interact with whatever you currently have or are planning to have. Also, you did not answer the specific question I asked about the manager and his vacation.

 

[jessicarabelli]

Well, it would be a problem if I give you the code I have in mind. What I planned to do is what Scott T did... give you a Worksheet_Change event procedure that would run automatically to block the placing of a duplicate V when it should not be placed. As an automatic procedure, you would not have the ability to override it unless I build that capability into the code. That is what I meant about not wanting to try and shoehorn in additional privileges afterwards. So maybe you need to give us a little more detail about how the code is supposed to interact with whatever you currently have or are planning to have. Also, you did not answer the specific question I asked about the manager and his vacation.

Rick, if it is possible for the manager to override the rule (with a password or something), that would be great. Otherwise, the easier way to override would be to write "vacation" instead of "v."

In regards to the question about my manager, if he chooses to take a vacation when other V's are already selected, I would like to keep the other Vs.

 

[jessicarabelli]

Well, it would be a problem if I give you the code I have in mind. What I planned to do is what Scott T did... give you a Worksheet_Change event procedure that would run automatically to block the placing of a duplicate V when it should not be placed. As an automatic procedure, you would not have the ability to override it unless I build that capability into the code. That is what I meant about not wanting to try and shoehorn in additional privileges afterwards. So maybe you need to give us a little more detail about how the code is supposed to interact with whatever you currently have or are planning to have. Also, you did not answer the specific question I asked about the manager and his vacation.

Please don't give up on me. I still need help