Data validation formula

[Solved/Closed]
Report
-
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
-
Hello,

I have a spread sheet that tracks attendance. What i want to do is to alert the user whenever 3 OR MORE CELLS IN SEQUENCE CONTAIN THE SAME DATA. eg. If someone calls in sick, a S is placed in the cell, if this person is sick Mon , Tues, Wed, I would like to alert the user. ( 3 days in sequence with the same data ). If the person is sick on Thur aswell, I want to alert the user again.

Need help with the formula for this.

Thanks

3 replies

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
Could you please upload a sample file with sample data etc on some shared site like https://authentification.site and post back here the link to allow better understanding of how it is now and how you foresee.
https://authentification.site/files/22317515/ATTENDANCE.xls

the only time i want the alert to show is if the letter S is repeated 3 or more times in sequence.
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
1. Press ALT + F11 to open VBE
2. Press CTRL + R to open Project Explorer
3. Double click on the sheet in which you want this message box
4. Paste the code

Private Sub Worksheet_Change(ByVal Target As Range)
Dim vPos As Variant
Dim iCol As Integer
Dim CellValue As Variant


    If ((Target.Columns.Count = 1) And (Target.Rows.Count = 1)) Then
        If Target = "" Then Exit Sub
    End If
    
    vPos = ""
    
    Application.EnableEvents = False
        
    For Each Cell In Target
        
        If UCase(Cell) <> "S" Then GoTo Next_Cell
            
        vPos = ""
        
        iCol = Cell.Column
        
        If iCol >= 3 Then
            
            If ((Cell = Cell.Offset(0, -2)) And (Cell.Offset(0, -1) = Cell)) Then
                vPos = -1
            End If
            
        End If
        
        If ((vPos = "") And (iCol >= 2) And (iCol < Columns.Count)) Then
            
            If ((Cell = Cell.Offset(0, -1)) And (Cell.Offset(0, 1) = Cell)) Then
                vPos = 0
            End If
        
        End If
        
        If ((vPos = "") And (iCol < Columns.Count - 1)) Then
        
            If ((Cell = Cell.Offset(0, 1)) And (Cell.Offset(0, 2) = Cell)) Then
                vPos = 1
            End If
                
        End If
        
        
        If (vPos <> "") Then
        
            GoTo End_Sub
        
        End If
    
Next_Cell:
  
    Next

End_Sub:

    Application.EnableEvents = True
    If (vPos <> "") Then
    
        MsgBox "Three in a row"
        
    End If
End Sub
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
Use the modified code.

Added one line

If (Target.Columns.Count > 1 Or Target.Rows.Count > 1) Then Exit Sub
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
I have just re-modified the code. Use this third version. It is much better and handle more possibilities.
Thanks so much.

I forgot to mention one thing. Can you modify so that it will give the same result if someone is sick on Friday, Monday, Tuesday. The weekends breakup the sequence. The current version only alerts you from Monday to Friday.
Is it possible to do the above ? thanks
rizvisa1,
Thanks so much.

I forgot to mention one thing. Can you modify so that it will give the same result if someone is sick on Friday, Monday, Tuesday. The weekends breakup the sequence. The current version only alerts you from Monday to Friday.
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
try this


Private Sub Worksheet_Change(ByVal Target As Range)
Dim vPos As Variant
Dim iCol As Integer
Dim CellValue As Variant
Dim iOffsetL2 As Integer
Dim iOffsetL1 As Integer
Dim iOffsetR1 As Integer
Dim iOffset2 As Integer

Dim CellL2 As Variant
Dim CellL1 As Variant
Dim Cell0 As Variant
Dim CellR1 As Variant
Dim CellR2 As Variant

    If ((Target.Columns.Count = 1) And (Target.Rows.Count = 1)) Then
        If Target = "" Then Exit Sub
    End If
    
    vPos = ""
    
  '  Exit Sub
    
    On Error GoTo End_Sub
    
    Application.EnableEvents = False
        
    For Each Cell In Target
        
        Cell0 = UCase(Cell.Value)
        'If Cell0 <> "S" Then GoTo Next_Cell
                
        vPos = ""
        iOffsetL2 = 0
        iOffsetL1 = 0
        iOffsetR1 = 0
        iOffsetR2 = 0
        
        iCol = Cell.Column
        
        If (IsDate(Cells(1, iCol))) Then
                       
            CellL2 = "Garbage Value"
            CellL1 = "Garbage Value"
            CellR1 = "Garbage Value"
            CellR2 = "Garbage Value"

            Select Case (Weekday(Cells(1, iCol), vbMonday))
            
                Case Is = 1
                    iOffsetL2 = -2
                    iOffsetL1 = -2
                    iOffsetR1 = 0
                    iOffsetR2 = 0
                    
                Case Is = 2
                    iOffsetL2 = -2
                    iOffsetL1 = 0
                    iOffsetR1 = 0
                    iOffsetR2 = 0
                
                Case Is = 4
                    iOffsetL2 = 0
                    iOffsetL1 = 0
                    iOffsetR1 = 0
                    iOffsetR2 = 2
                
                Case Is = 5
                    iOffsetL2 = 0
                    iOffsetL1 = 0
                    iOffsetR1 = 2
                    iOffsetR2 = 2
            End Select
        End If
        
        On Error Resume Next
            CellL2 = Cell.Offset(0, (-2 + iOffsetL2)).Value
            CellL1 = Cell.Offset(0, (-1 + iOffsetL1)).Value
            CellR1 = Cell.Offset(0, (1 + iOffsetR1)).Value
            CellR2 = Cell.Offset(0, (2 + iOffsetR2)).Value
        On Error GoTo End_Sub
        
        CellL2 = UCase(CellL2)
        CellL1 = UCase(CellL1)
        CellR1 = UCase(CellR1)
        CellR2 = UCase(CellR2)
        
        If (iCol + iOffsetL2 > 2) Then
            
            ' ? ? X
            If ((CellL2 = Cell0) And (CellL1 = Cell0)) Then
                vPos = -1
                GoTo End_Sub
            End If
            
        End If
        
       
        If ((iCol + iOffsetL1 > 0) And ((iCol - iOffsetR1) < Columns.Count)) Then
            
            ' ? X ?
            If ((CellL1 = Cell0) And (Cell0 = CellR1)) Then
                vPos = 0
                GoTo End_Sub
            End If
        
        End If
        
        
        If (iCol < Columns.Count - 1) Then
        
            ' X ? ?
            If ((Cell0 = CellR1) And (Cell0 = CellR2)) Then
                vPos = 1
                GoTo End_Sub
            End If
                
        End If
    
Next_Cell:
  
    Next

End_Sub:

    Application.EnableEvents = True
    If (vPos <> "") Then
    
        MsgBox "Three in a row"
        
    End If
End Sub

The only time I want that message to show is when the S is entered 3 or more times. I want to be able to enter all other data without that message appearing. Can that be fixed ?
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
'If Cell0 <> "S" Then GoTo Next_Cell


remove the " ' " before the line.
thanks. works perfect.
Can you remodify the code you wrote to get the following done.

This is how I envision this working. At the beginning of the year column L will be filled in with a range of numbers ranging from 0 to a maximum value of 10, at this point whenever an S is entered 1 must be subtracted from the total in column L, if this total is 10 then it would then be 9, if the total is already zero, then the message box would appear at this point, prompting the user that there are no more sick days available. The message box only appears when the total in column L is at zero and the user attempts to enter S.

The employee gets back his sick days on his anniversary date, eg. the hire date for someone is 1-20-90, the number in column L has to automatically be reset to 10 on this date, 1-20-2011, so if the number in Column L was 2, then it would go back to 10. When this happens the cycle outlined above will repeat itself.

See attached for more details.

https://authentification.site/files/23740704/Copy_of_SAMPLE.xls
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
May be I am having a brain freeze here, but it is not as simple as you would like it to be. There are too many if and buts.