Worksheet Event_Array

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

  • Re: Worksheet Event_Array

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

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

    Change it to:

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

    1. Target.Value = vbNullString

    for this:

    1. Application.EnableEvents = False
    2. Target.Value = vbNullString
    3. Application.EnableEvents = True
  • Re: Worksheet Event_Array

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

    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why