Dates in TextBoxes

[James]

Hi

I have a Form with a TextBox in which a user is supposed
to enter a date, to be used in a calculation later on.
This is giving me a couple of headaches:

(a) How do I go about providing validation for the
TextBox.Value? Currently I have a line that looks like:

If TextBox.Value >= 1/04/2004 And _
TextBox.Value <= 31/3/2005 Then

This doens't work, since something like 1/04/02 returns
TRUE for the above If statement

(b) When I use the TextBox.Value to update a cell on my
worksheet, the value returned is a text value, ie. can't
use it in the calculation. What am I doing wrong?

Thanks in advance,

James
(new to VBA)

 

[Tom Ogilvy]

dim dt as Date
Dim sStr as String
sStr = Textbox1.Text
if isdate(sStr) then
dt = cDate(sStr)
ActiveCell.Value = dt
if dt >= #4/1/2004# and dt <=#3/31/2005" then
ActiveCell.Formula = "dd/mm/yyyy"
Else
msgbox "Out of bound"
end if
Else
msgbox "Not a date"
End if

Generally in VBA, you should work with US formatted dates. Cdate should
convert (interpret) the string to a date serial consistent with your
regional settings.

 

[Jacques brun]

James,
I suggest you convert the input string into a date
using the DateValue function and appropriate error handling
to cope with invalid input.

Consider the test subroutine below. Ive used an inputbox
rather than a form to get the date, but it is similar to
what you describe. You probably can use the same logic to
validate the entry in your form.

Regards
Jacques
==========================================================

Sub Test()
Dim rrr As Long
Dim xxx As String
Dim DDD As Date

On Error GoTo ErrHdlr
xxx = InputBox("Enter date ")
rrr = 1
DDD = DateValue(xxx)
If DDD < #4/1/04# Then
rrr = 2
GoTo ErrHdlr
ElseIf DDD > #3/31/05# Then
rrr = 3
GoTo ErrHdlr
End If
On Error GoTo 0
GoTo OKContinue

ErrHdlr:
MsgBox Choose(rrr, "Invalid Date format", _
"Date is too Old", _
"Date is too far") & " : " & xxx

Exit Sub

OKContinue:
MsgBox "Good date " & xxx
End Sub

 

[Patrick Molloy]

Dim textboxdate As Date
Dim firstdate As Date
Dim seconddate As Date

firstdate = DateSerial(2004, 4, 1)
seconddate = DateSerial(2005, 3, 31)

If IsDate(TextBox.Text) Then
textboxdate = CDate(TextBox.Text)

If textboxdate >= firstdate _
And textboxdate <= seconddate Then

' process
TextBox1.Text = "ok"

End If

End If


note...date literals are entered this way
date1 = #1/4/04#
this is "dangerous" since often the date style is
American...try it
enter a line like
date1 = #1-apr-2004#
and the compiler wil change this
#4/1/04#
while in the uK I'd expect #1/4/04#
hence the use of the dateserial function. It's
unambiguous!


Patrick Molloy
Microsoft Excel MVP