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

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
--
Gary's Student


"hui" wrote:

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

thanks, very nice

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

thank you

hui
"Gary''s Student" ...
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
--
Gary's Student


"hui" wrote:

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