A validation rule on Alpha and Numeric characters
I want to create a validation that when i enter a mixed numeric and alpha
reference that i can prevent changing its format.
For example:
Everytime i enter my "number" FEE1234, that the cell keeps the formatting
"@@@####" and prevents me using any other format indicating n error
message.
Another possibility, with special note to Harlan's remark in his reply to my
other post in this sub-thread, namely,
"Event handlers only work when macros are enabled,
and it's simple for users to disable macros"
is to create your own format checker via a macro. Here is a function macro
that should be usable which will expose VBA's Like operator for use in a
worksheet. Right click the tab at the bottom of the sheet, select "View
code", add a Module to the worksheet and put this code in it...
Public Function IsLike(R As Range, Pattern As String) As Boolean
Dim C As Range
IsLike = True
For Each C In R
If Not C.Value Like Pattern Then
IsLike = False
Exit Function
End If
Next
End Function
For your stated need, just put the following into a helper column starting
at the first cell you want to check and then copy down...
=IsLike(A1,"[A-Z][A-Z][A-Z]####")
Of course, replace the A1 with the actual cell reference you want to check.
Rick
|