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:


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


    Change it to:


    Code
    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:


    Code
    1. Target.Value = vbNullString


    for this:


    Code
    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:


    Rory
    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