data validation through Worksheet Change Event

[robertvdb]

I have a sheet with hundreds of lines in col A.

When I ADD a NEW line below, the value in the Range("A" & newline) must match either of the values apples/bananas/oranges.

Is there a way to do this using the Worksheet Change Event, instead of using data validation ?

Thanks.

 

[Peter_SSs]

Is this what you mean? It removes any entries that do not match A2:A4

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Changed As Range, c As Range
  
  Set Changed = Intersect(Target, Columns("A"), Rows("5:" & Rows.Count))
  If Not Changed Is Nothing Then
    Application.EnableEvents = False
    For Each c In Changed
      If Not IsNumeric(Application.Match(c.Value, Range("A2:A4"), 0)) Then c.ClearContents
    Next c
    Application.EnableEvents = True
  End If
End Sub

 

[robertvdb]

Thanks Peter

your solution is valuable, however I would prefer something like:

- if it's a NEW row below the existing ones, then the new entry should match the values in an array ("apples","bananas","oranges")

so I need to determine the last used row
and then check the new entry.

 

[Peter_SSs]

I'm not following.

1. Can you give an example where my code doesn't do what you want.
2. Are you saying the list to check against ("apples","bananas","oranges") is unrelated to what is above? That is, if A2:A4 contained "plums", "cherries" & "apricots" then A5 would still have to be one of "apples","bananas","oranges"?

 

[robertvdb]

Thanks Peter. Let me see if I can explain this a little better.

Set Changed = Intersect(Target, Columns("A"), Rows("5:" & Rows.Count))

here Changed should refer to the new Row.

Are you saying the list to check against ("apples","bananas","oranges") is unrelated to what is above? That is, if A2:A4 contained "plums", "cherries" & "apricots" then A5 would still have to be one of "apples","bananas","oranges"?

YES. A2:A4 are old entries. The check only applies to new entries.

 

[Peter_SSs]

Set Changed = Intersect(Target, Columns("A"), Rows("5:" & Rows.Count))

here Changed should refer to the new Row.

So what happens if

1. the user enters something in a row below the first empty one? Don't check that entry?
2. the user enters (or pastes) multiple values in to the next row and other rows immediately after that? Only check the first row of the multiple rows entered?
3. the user replaces an entry (or multiple entries) that already exists above that "new row"? Don't check such entries?
4. the user enters "APPLES" instead of "apples" etc?

 

[robertvdb]

it's getting too complicated for me I guess... I'll stick to the less complicated and built in Data Validation.

Thanks anyway for your help.

 

[Peter_SSs]

I'll stick to the less complicated and built in Data Validation.

Up to you, but presumably the Data Validation will be in multiple rows, not just the next empty one.

Also just pointing out that both DV and using vba have possible drawbacks
Data Validation can be defeated if data is copy/pasted into cells that contain DV
vba can be defeated if the user does not have macros enabled

 

[robertvdb]

Thanks Peter. Your initial suggestion deserves to be marked as a possible solution. Which I did just now.