Customized cell validation!
 

How do I set Customized validation to allow first alpha two digits an
follow by two numeric digits. (Like CN15)
Thank you for your help!
Kim-An

--
Message posted from http://www.ExcelForum.com

Customized cell validation!
 

Here is some worksheet event code

Private Sub Worksheet_Change(ByVal Target As Range)
Dim fOK As Boolean

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A1:A10")) Is Nothing Then
With Target
fOK = False
If Len(.Value = 4) Then

If UCase(Left(.Value, 1)) = "A" And _
UCase(Left(.Value, 1)) <= "Z" Then

If UCase(Mid(.Value, 2, 1)) = "A" And _
UCase(Mid(.Value, 2, 1)) <= "Z" Then

If Mid(.Value, 3, 1) = 0 And _
Mid(.Value, 3, 1) <= 9 Then

If Mid(.Value, 4, 1) = 0 And _
Mid(.Value, 4, 1) <= 9 Then

fOK = True

End If
End If
End If
End If
End If
End With
If Not fOK Then
MsgBox "Invalid value"
End If
End If

ws_exit:
Application.EnableEvents = True
End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Kim-Anh Tran " wrote in
message ...
How do I set Customized validation to allow first alpha two digits and
follow by two numeric digits. (Like CN15)
Thank you for your help!
Kim-Anh


---
Message posted from http://www.ExcelForum.com/

Customized cell validation!
 

How do I set Customized validation to allow first alpha two digits and
follow by two numeric digits. (Like CN15)
Thank you for your help!

try this:

=(LEFT(A1,1)="A")*(MID(A1,2,1)="A")*(MID(A1,3,1) <="9")*(RIGHT(A1,1)<="9")*(LEN(A1)=4)

--
Regards

Melanie Breden
- Microsoft MVP für Excel -

http://excel.codebooks.de (Das Excel-VBA Codebook)