how to set column check

[hui]

I made a worksheet store employee information. I hope when someone write
data in Column Birthday (Formated YYYY-MM-DD), the worksheet would
automatically check if the data is formated with YYYY-MM-DD, if not, it
would warn user. is it ok in worksheet?

thanks in advance.

hui

 

[Guest]

This uses column A as an example.
First format the cells in column A as text.
Then enter the following macro in wroksheet code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Columns("A")) Is Nothing Then
Exit Sub
End If
Dim v As String
v = Target.Value
v2 = WorksheetFunction.Substitute(v, "-", "")

If Len(v2) <> 8 Then
MsgBox ("bad input")
Exit Sub
End If

If IsNumeric(v2) Then
Else
MsgBox ("bad input")
Exit Sub
End If

v3 = Split(v, "-")
If Len(v3(0)) <> 4 Or Len(v3(1)) <> 2 Or Len(v3(2)) <> 2 Then
MsgBox ("bad input")
Exit Sub
End If

If v3(1) > 12 Or v3(2) > 31 Then
MsgBox ("bad input")
Exit Sub
End If
End Sub

 

[hui]

thanks, very nice

I always focus on worksheet functions. you told me another way to resolve
my problem.

thank you

hui