MrExcel Publishing
Your One Stop for Excel Tips & Solutions

This is a very tough question


Posted by Andres on April 10, 2001 4:34 PM

I'm making a date calculator, it finds the days and years between two dates. The problem is that sometimes the person just types the date like this " 05/03/85 " and I don't want that, I want the person to write the whole date like " 05/03/1985 ", so if the person writes the date like this " 05/03/85 " I want to be able to make a message box that might say " You must fill in the boxes correctly. ", thats all tnx for your help.

Andres


Posted by salvatore on April 10, 2001 7:05 PM

Maybe if you use data/validation you could get the right combination of values and format to allow or deny to users to input data.

Posted by Dave Hawley on April 10, 2001 8:36 PM


Hi Andres

You asked this a few days ago and I supplied the following:

I'm assuming your talking about a UserForm with textboxes, if so try the code below. It requires a Userform, TextBox1, Textbox2 and CommandButton1

Option Explicit
Dim DateCheck2 As Date
Dim DateCheck1 As Date

Private Sub CommandButton1_Click()
MsgBox DateDiff("d", DateCheck2, DateCheck1)
End Sub

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)

If Not IsDate(TextBox1) Then
MsgBox "Your date is not valid, please correct it.", vbCritical, "OzGrid Business Applications"
Cancel = True

Else
DateCheck1 = TextBox1.Value
TextBox1 = Format(TextBox1, "dd/mm/yyyy")

End If
End Sub


Private Sub TextBox2_Exit(ByVal Cancel As MSForms.ReturnBoolean)

If Not IsDate(TextBox2) Then
MsgBox "Your date is not valid, please correct it.", vbCritical, "OzGrid Business Applications"
Cancel = True

Else
DateCheck2 = TextBox2.Value
TextBox2 = Format(TextBox2, "dd/mm/yyyy")

End If
End Sub


Dave

OzGrid Business Applications

Posted by Andres on April 11, 2001 3:01 PM

Dave it doesnt work

I tried the code you gave me, but it doesnt work. The part where it says " Private Sub TextBox1_( This right here part the computer wont accept it).
So I dont know. See if it works for you.

Andres

Posted by nigs on April 12, 2001 10:50 AM

Hey Andres,

The dates used in the calculations; can you set it
up so that the dates are driven by a range in
the worksheet...if you are using a form or data validation
the dates for the calcs will have to be selected
from the range.(this the user has to selet fromt the
list in the format specified)..assuming that the dates can be put
into a range. even if the range is huge, this may work
with a spin button as it will allow for selection
of vast ranges...hope it helps