I want to restrict user input entry on worksheet cell, not mentioned in array find below mentioned code and example. this code is not working don't know the reason.
i dont want to use data validation on worksheet for this. i will appreciate any help on this.
e.g if user enter 4 in range A2 it will not allow and show error alert, user can enter only 1,2,3 values on worksheet.
Code
Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
Dim arr() As Variant
Dim lng As Variant
Dim rs As String
arr = Array("1", "2", "3")
If Not Application.Intersect(Target, Range("$A$2:$A$10")) Is Nothing Then
rs = Target.Value
lng = Application.WorksheetFunction.Match(rs, arr(), 0)
If IsEmpty(lng) Then
MsgBox "Invalid Entry", vbCritical, "Error"
Target.Value = vbNullString
End If
End If
End Sub
Display More