want to lock cells in a row in attendance tracker

[sunilbsrv2k]

Hi All,

I have a question in Excel/VBA.

I am creating an Attendance tracker for about 200 team members; in which each row corresponding to a team member displays their attendance for the entire month.

There are 3 types of leaves: CL, SL and EL.

The last 3 columns of the table show the total number of leaves taken by the member in each type mentioned above.

Now, the question is, if a person crosses 2 in CL, then that entire row should be disabled from entering another CL; however, it should still allow me to update SL and EL in all other cells.


Could any of you help me on this

Thanks,

 

[sunilbsrv2k]

Hi All,

I might have complicated my question.

Let me simplify this in one line:

I have a row in which if I enter CL 2 times, then it should not allow me to enter CL (only) again in the specified range.

Thanks

 

[DanteAmor]

Let's start with the following, if I understood your idea.
Put the following code in the events of your sheet.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim r As Range, c As Range
    Set r = Range("A1:D10")
    On Error GoTo appEnable
    If Not Intersect(Target, r) Is Nothing Then
        If Target.Count > r.Count Then Exit Sub
        Application.EnableEvents = False
        For Each c In Target
            If UCase(c.Value) = "CL" Then
                wcount = WorksheetFunction.CountIf(r, c.Value)
                If wcount > 1 Then
                    MsgBox "not allow"
                    c.Value = ""
                End If
            End If
        Next
        Application.EnableEvents = True
    End If
appEnable:
    Application.EnableEvents = True
End Sub

SHEET EVENT
Right click the tab of the sheet you want this to work, select view code and paste the code into the window that opens up.