Instead of using the built in Data Validation, you can roll your own using
VBA and the worksheet change event. Copy the code below, right click on the
sheet tab, select "View Code", and paste the code in the window that
appears. The code is written to validate entries in column B, but that can be
easily changed (as can the message). The code may not be very readable
because of the continuatuons I used to defeat line wrapping errors, but it works.
HTH,
Bernie
MS Excel MVP
Private Sub Worksheet_Change(ByVal Target As Range)
Dim myCode As String
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Range("B:B"), Target) Is Nothing Then
Application.EnableEvents = False
myCode = Target.Value
If (myCode Like _
"[0-9][0-9][0-9].[0-9][0-9][0-9][0-9][0-9]") _
Or (myCode Like _
"[0-9][0-9][0-9].[0-9][0-9][0-9][0-9][0-9]." & _
"[0-9][0-9][0-9]") _
Or (myCode Like _
"[0-9][0-9][0-9].[0-9][0-9][0-9][0-9][0-9]." & _
"[0-9][0-9][0-9].[0-9][0-9][0-9]") _
Or (myCode Like _
"[0-9][0-9][0-9].[0-9][0-9][0-9][0-9][0-9]." & _
"[0-9][0-9][0-9].[0-9][0-9][0-9].[0-9][0-9][0-9]") Then
Else
Application.Undo
MsgBox "That was an invalid entry in cell " _
& Target.Address(False, False) & "."
End If
Application.EnableEvents = True
End If
End Sub
luvgreen said:
Thank you for looking.
It should be like "XXX.XXXXX" or "XXX.XXXXX.XXX" or "XXX.XXXXX.XXX.XXX" or
"XXX.XXXXX.XXX.XXX.XXX"
X represents number. The first 3 numbers followed by a dot and 5 numbers,
after that it is always followed by a dot and 3 numbers. How can I validate
that? Thanks.
