Validate cell value against a list

[Guest]

When my user completes entry into the spreadsheet they press a button that
looks through all the cells and checks for certain error conditions (no
commas, must be numeric ...).
I would like to add a event that checks the value in the cell to the list of
values assigned to it. Most of my fileds have drop down lists (created as
lists) but not all so I would need to be find out if the list exists for the
cell and then if the cell value is valid.
Most of my issues around this are from the users pasting values into the
cells and bypassing the list validation.

Thanks in advance for any help. This group has been very helpful

 

[Tom Ogilvy]

When the user pastes a value, they probably destroy your data validation
criteria as well (in the act).

That said, you can probably use the change event

see Chip Pearson's page on Events

http://www.cpearson.com/excel/events.htm

 

[Bernie Deitrick]

Hippy,

To prevent pasting, you could use an event. For example, copy the code below, right-click the sheet
tab, select "View Code", and paste the code into the window that appears. It will prevent pasting
into cells B5 and C6 on that sheet.

You can modify the range to include all cells where you don't want the user to be able to bypass
validation using paste.

HTH,
Bernie
MS Excel MVP

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Intersect(Target, Range("B5,C6")) Is Nothing Then Exit Sub
If Application.CutCopyMode Then
Application.CutCopyMode = False
MsgBox "Sorry, you can't paste values into those cells!"
End If
End Sub