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.
- 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