Date validation in userform

[John Guderian]

I have a field that I would like to have users enter in a date. I have
created the text box, and have code that updates a bookmark in the document
with the text in the textbox, however I dont see any options for forcing
someone to enter the date as mm/dd/yyyy, and if its incorrect notify them. I
just want it to make sure its entered properly, and that the date is a valid
date.

Am I missing something? How can I make this happen? I am a complete novice
with VBA, so any info would be greatly appreciated.

Thanks,
John

 

[Greg]

John,

Consider:

Sub Test()
Dim oInput As String
Dim oMyDate As Date
Retry:
oInput = InputBox("Enter the date in mm/dd/yyyy format: ")
If IsDate(oInput) Then
oMyDate = Format(oInput, "mm/dd/yyyy")
MsgBox oMyDate
Else
MsgBox oInput & " is not a valid date format."
GoTo Retry
End If
End Sub

 

[John Guderian]

Greg thanks for the help.

This seems to work with a couple of problems. Ive got a text box which upon
"Enter" executes the sub you wrote. A new text box appears, and validates
the date, and I have the result being written to the text box
(txtEffectiveDate). However it prompts the date entry text box again once
the date is entered into the text box. How can I get the results written to
my text box without it launching twice? Also the cancel button doesnt cancel
it continues to validate the "null" value.

Thanks again. This almost works prefectly!

 

[John Guderian]

Greg,

I relaized why the inputbox was running after entry... I had the sub running
on enter, and change. Oops.

However, I would like to get rid of the inputbox, and simply be able to type
the date in my text box (txteffectivedate), have the sub validate the date,
and if incorrect, must change before clicking ok, or when clicking ok ensure
that its correct, and prevent somene from exiting the form without a correct
date.

I can successfully have the sub validate the text box, and alert me that its
incorrect, however when someone clicks ok, the form disappears, and the date
is entered wrong. I dont know how to prevent the rest of the code within
"cmdOK_click" from running until the correct date is enetered. Right now,
when I click ok it will eneter in any info in the text box, and then close
the form. Id like your sub to validate the text box, and if correct continue
with the code, otherwise force me to enter a correct date, and dont continue
with the code.

I think Im close, but Im very unfamiliar with VB or VBA to successfully
troubleshoot this.

Your help is appreciated

John

 

[Greg]

John,

Too busy to provide a complete answer now as I don't have a sample
userform to work with and won't until I get home.

You probably don't even need to run the whole script I provided as a
separate routine. You could include the relavent parts in cmOK_click
routine. If get to the Msgbox, then just include an Exit Sub line
immeadiately afterword. Not tested, but something like:

Sub cmdOk_Click()

'Your existing code
With Me
If IsDate(.txteffectivedate.text) Then
.txteffectivedate.text = Format(txteffectivedate.text, "mm/dd/yyyy")
Else
MsgBox .txteffectivedate.text & " is not a valid date format."
With .txtefffecivedate
.SetFocus
.SelStart = 0
.SelLength = Len(.Text)
End With
Exit Sub
End If
End With
'The rest of your code.
End Sub

 

[Greg Maxey]

John,

I just tested this. You should be able to use it directly in your
cmdOk_Ckick routine:

Private Sub cmdOk_Click()
With Me
If IsDate(.txteffectivedate.Text) Then
.txteffectivedate.Text = Format(.txteffectivedate.Text, "mm/dd/yyyy")
Else
MsgBox .txteffectivedate.Text & " is not a valid date format."
With txteffectivedate
.SetFocus
.SelStart = 0
.SelLength = Len(.Text)
End With
Exit Sub
End If
End With
'Rest of your code here
Me.Hide
End Sub

 

[John Guderian]

Greg - this works great! Thank you very much! As I was creating this form I
relaized that I will have several date fields. Can I simply duplicate this
code for each field? Or better yet, can this be modified into a subroutine
that I call with the specific fields I want validated?

Also I noticed that the first time I click OK it doesnt set the focus on the
text box, however the second time I hit OK it does. Why do you suppose?

I apologize for asking what maybe rudimentary VBA questions, but I do
appreciate all the guidance youve offered.

Thanks,
John

 

[Greg Maxey]

John,

Without seeing your complete code, I can't explain why you couldn't set the
focus the first attempt. There is a chance I couldn't explain it after
looking at your code ;-). I am not to far removed from a beginner myself.
Walking now, but falling down a lot and constantly bumping into things ;-).
You certainly could create a routine to step out and run the validation
checks on a series of text fields.

Why don't you download and have a look at the code in my Date Sequencer
Add-in. It has lots of code to manipulate date entries.

http://gregmaxey.mvps.org/Date_Sequencer.htm