Validate all cell values against it's list prior to saving the she

[Guest]

Original Question:

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


After further investigation:

I was able to get dynamic validation to work using the following code. I
used a loop to go thought the list of values until I found an exact match. Is
there a command in VB that would do this instead of a loop (like the Excel
"Match" command)?

Private Function ValidateLOVValue(pcellin As Range) As Boolean
Dim r As Range
Dim n As Integer
Dim r1 As Boolean
On Error GoTo errHandler

If pcellin.Validation.Type = 3 Then
' check if there is a list of values validation necessary
r1 = False

Set r = Range(pcellin.Validation.Formula1)
For n = 1 To r.Rows.Count
If r.Cells(n, 1) = pcellin.Value Then

ValidateLOVValue = True
Exit Function

End If
Next n

If r1 = False Then
Call setCellStateToError(pcellin, "Value not in list of values for " &
pcellin.Validation.Formula1 _
, False)
ValidateLOVValue = False
Exit Function
End If
ValidateLOVValue = True
Exit Function
End If

errHandler:

ValidateLOVValue = True

End Function

 

[Tom Ogilvy]

Yes, you can use the Excel match function.

Dim res as variant
res = Application.Match("abcd",Range("a1:A200"),0)
if not iserror(res) then
msgbox "Found"
Else
msgbox "Not found"
End if

 

[Guest]

Thanks Tom it worked great and cut my validation time quite a bit!