Worksheet Event_Array

Mahesh1984s · Dec 12th 2016

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

shknbk2 · Dec 12th 2016

Re: Worksheet Event_Array

Get rid of the parentheses of "arr()" in this line:

Code

lng = Application.WorksheetFunction.match(rs, arr(), 0)

Change it to:

Code

lng = Application.WorksheetFunction.match(rs, arr, 0)

Also, you will want to stop the procedure from running in a loop every time you set the Target.Value to vbNullString. Substitute this line:

Code

Target.Value = vbNullString

for this:

Code

Application.EnableEvents = False
Target.Value = vbNullString
Application.EnableEvents = True

**rory** · Dec 13th 2016

Re: Worksheet Event_Array

Rather than clearing the cell you might also want to reinstate the previous value, if any:

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"
With Application
.EnableEvents = False
.Undo
.EnableEvents = True
End With
End If
End If
End Sub

Display More

Share