Excel Date Entry Question 2

[Leslie67]

I've got a form that's going to be used for data entry.

I have cell formatting on so that dates entered come out as "November 3, 2003".

This works fine if someone enters "11/03/03", however when you enter "110303" you get the date relative to January 1, 1900.

Does anyone have a solution? My problem is that a lot of different people will be using this form, so I'm trying to anticipate all kinds of data entry for the date.

 

[Leslie67]

Skip:

You and Bowers are both right.

It's an interesting mental exercise, but I'm off to convince my boss that it's futile and that the validation route is much better.

Thank you very much for your assistance... it was very much appreciated.

 

[ladyck3]

Bowers74,

With your Data Validation formula:

=AND(MID($A$1,3,1)<>&quot;/&quot;,MID($A$1,6,1)<>&quot;/&quot


Is there a way to force 3 initials prior to the date?

Thanks...
ladyck3

 

[Bowers74]

I have the worksheet at home, and I don't have time at work right now to check it, but I will let you know as soon as I get home in about 2 hours!





Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[Bowers74]

ladyck3,

I couldn't get it to work, and I realized that there are some &quot;bugs&quot; in my Data Validation plan anyway. So I decided to code it.

You need to create a Named Range for the cells that will be containing these dates:

Select the cells and goto Insert->Name->Define.... You selection should still be in the &quot;Refers to:&quot; field. Enter &quot;Dates&quot; in the top field (w/o quotes).

Now Press Alt+F11 to get to the VBA editor and find your sheet in the tree view on the left. Double Click it to enter the code module then paste this code into it:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myDates As Range
Set myDates = ThisWorkbook.ActiveSheet.Range("Dates")
If Union(Target, myDates).Address = myDates.Address And _
    Not IsEmpty(Target) Then
    If Not Target Like "??? *#/*#/####" Or IsNumeric(Mid(Target, 1, 1)) _
        Or IsNumeric(Mid(Target, 2, 1)) Or IsNumeric(Mid(Target, 3, 1)) Then
        MsgBox "Wrong Format"
    Else
        Target = UCase(Left(Target, 3)) & Mid(Target, 4, 11)
    End If
End If
End Sub

If you did this right, then you shouldn't be able to enter ANYTHING except three letters, a space, and a date (in the m/d/yyyy format). The initial will be automatically capitalized.

I hope this helps!

Good Luck!



Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[ladyck3]

A star for you.... you are the top go-getter for a reason

Thanks much!!!

Ladyck3

 

[Bowers74]

Leslie67,

a variation of this should work for you too, but since you are worried about one cell at a time, you need to add an Or Target.Address = &quot;$A$1&quot; into the Worksheet_Change event for each cell that will contain the date (change the $A$1 to the address for each cell). Plus you need a Worksheet_SelectionChange as well. You can use the following procedures:

Private Sub Worksheet_Change(ByVal Target As Range)
If IsEmpty(Target) Then
    Target.NumberFormat = "General"
ElseIf Target.Address = "$A$1" Or Target.Address = "$A$2" Then
    If Not Target Like "*#/*#/####" Then
        MsgBox "Wrong Format", vbInformation, "Invalid Format"
        With Target
            .Value = ""
        End With
    End If
End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = "$A$1" Or Target.Address = "$A$2" Then
If IsEmpty(Target) Then Exit Sub
    If MsgBox("Do you want to change the date?", vbQuestion _
        + vbYesNo, "Change Date") = vbNo Then Exit Sub
        With Target
            .Value = ""
            .NumberFormat = "General"
        End With
End If
End Sub

I hope this helps!



Peace!

Mike

Never say Never!!!
Nothing is impossible!!!