Wondering if anyone has had success masking date entries in Excel? My objective is to ensure that '82107', '082107', '8/21/07', '08/21/07' and '08/21/2007' are all formatted/recognized as a date value so that additional calculations can occur following data entry by a user. My knowledge of Excel is limited compared to Access, however, I was hoping to apply formatting/date conversion upon a specified worksheet change. The code below will change a string entered as 082107 to a date value, but then breaks as it loops thru a second time.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim StrVal As String
Dim dDate As Date
Dim int1 As Integer
StrVal = Format(Range("MyDate").Value, "000000")
If int1 = 0 Then
If InStr(StrVal, "/") > 0 Then
If IsNumeric(StrVal) And Len(StrVal) = 6 Then
dDate = DateValue(Left(StrVal, 2) & "/" & Mid(StrVal, 3, 2) & "/" & Right(StrVal, 2))
Actually, I am using data validation (custom), but to force the entry of a date = to the current calendar year. The code does the trick on the first pass, but it breaks on the second time thru. I think it's a matter of eliminating the loop. I can give the data form a try in the interim. Thank you.