My question is how can i set stop data entry when i exceed the limit of 3 countifs of same participant?

Question

My question is if I entered "JOHN" 4 times then show me a error or stop message "You exceed more than 3, Enter a new name"

"max of participants list is 3"

Answer 1

This should work, or at the very least, get you started. The macro will only be called upon when a cell is changed in Column B. When fired, the macro will display a message and clear the entire row of the changed cell IF the Participant name exists more than 3 times in the column.

You do not need to keep Column D for this macro as it calculates the count independently. There is no harm in leaving it there either though if you need to see the remaining possible counts (1, 2, 3)

---

To implement, paste this code on the worksheet in VB Editor where your code is stored.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column = 2 Then
    If Application.WorksheetFunction.CountIf(Range("B:B"), Target) > 3 Then
        Application.EnableEvents = False
            MsgBox "Participant Already Has 3 Entries!"
            Target.EntireRow.Clear
        Application.EnableEvents = True
    End If
End If

End Sub