Solved: Force user to correctly enter date

[Gingertrees]

Hi, I'm new to VBA and know precious little about the Excel variety, please help!
I have a spreadsheet where users enter info (name, birthdate, etc) on one sheet, and that populates other sheets. The problem is some people can't figure out how to correctly enter a date! I have a couple users who keep entering dates w/o punctuation (06122008) or with weird punctuation (06\12\008). How to fix?
I'd like to invoke a msgbox with something like "Hey buckethead that's not a valid date!", and make the user unable to proceed til he fixes it. Can that be done?

[Bob Phillips]

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "H1:H10" '<== change to suit

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target

If Not IsDate(.Value) Then

MsgBox "Hey buckethead that's not a valid date!"
.Value = ""
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub


This is worksheet event code, which means that it needs to be
placed in the appropriate worksheet code module, not a standard
code module. To do this, right-click on the sheet tab, select
the View Code option from the menu, and paste the code in.

[Tommy]

Well mine is not as well written as xld's but here you go :)


Function CheckDate(DateEntered) As Boolean
If Not IsDate(DateEntered) Then
MsgBox "HEY BUCKETHEAD!!" & vbCrLf & "INVALID DATE HAS BEEN ENTERED!!!!!!!" & _
vbCrLf & "THIS IS UNEXCEPTABLE!!!!" & vbCrLf & _
"THIS IS AN EXCEPTABLE ENTRY-> 03/03/2008 <-" & vbCrLf & _
"NOTE THE DIRECTION OF THE SLASHES!", vbOKOnly, _
"DATES ARE THE ONLY ACCEPTABLE INPUT"
Else
CheckDate = True
End If
End Function

[W@ll]

Hi, I'm new to VBA and know precious little about the Excel variety, please help!
I have a spreadsheet where users enter info (name, birthdate, etc) on one sheet, and that populates other sheets. The problem is some people can't figure out how to correctly enter a date! I have a couple users who keep entering dates w/o punctuation (06122008) or with weird punctuation (06\12\008). How to fix?
I'd like to invoke a msgbox with something like "Hey buckethead that's not a valid date!", and make the user unable to proceed til he fixes it. Can that be done?
would you like to correct automatically the dates or just just put a Alert msg?

a Alert msg, you'd use the Validation mode.

on toolbar:

<datas> <validation...>

In this window, you select "Dates" in criterion dropdown. Select a initial date n' a final date (maybe 12/31/2999).
at other flags, you select what kind of Msg this one would be: "information"; "Exclamation"; "Critical" and tipe the msg which you want to show.

sorry my english, I'm brazilian, but if you understand or not, please, reply on this e-mail:
wallace dot sertori at gnatus dot com dot br
I hope it's useful

good night

[mdmackillop]

Hi W@ll,
Please don't post your email address as a link. Generally, responses should be made on the forum for the benefit of others, or exceptionally by PM
Regards
MD

[Gingertrees]

W@ll : thanks! I didn't think about using data validation. That was so simple. Your english was fine (and much better than my non-existant Portuguese!) I understood exactly what you meant.

~Gingertrees