Customized cell validation!

  • Thread starter Thread starter Kim-Anh Tran
  • Start date Start date
K

Kim-Anh Tran

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
 
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)
 
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!
Click to expand...

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)
 
Back
Top