Getting r/t error

[JMay]

After entering say 11/27/44 in my TextBox 19 I want Textbox20 to display 63,
but it is not working

Anyone see why?

Private Sub TextBox19_AfterUpdate()
UserForm1.TextBox20.Text = Application.WorksheetFunction. _
DateDif(Format(DateValue(TextBox19.Text), "mm/dd/yyyy"), Date, "y")
End Sub

TIA,

 

[ShaneDevenshire]

Hi,

Your textbox returns text and the DateDif function needs a legal date try
using CDate
Look it up in the VBA help.

CDate(TextBox19.Text)

Also, I don't see any reason to apply the Format command, if the user does
not enter the data in a legal date format this code is not going to help.

If this helps, please click the Yes button.

 

[JMay]

Thanks Shane,
But where do I put the statement
CDate(TextBox19.Text)
and what do i eliminate from what I now have?

 

[Dave Peterson]

Private Sub TextBox19_AfterUpdate()
if isdate(me.textbox19.text) then
me.TextBox20.Text = Application.WorksheetFunction. _
DateDif(Format(DateValue(me.TextBox19.Text), "mm/dd/yyyy"), Date, "y")
end if
End Sub

(Untested, uncompiled!)

 

[JMay]

Thanks Dave (as usual) -- I tried your code, but didn't work. I've since
modified to:

Private Sub TextBox19_AfterUpdate()
MyBDate = CDate(TextBox19)
UserForm1.TextBox20 = Application.WorksheetFunction.DateDif(MyBDate,
Date, "y")
End Sub

But still getting r/t error 438 - Object doesn't support this prop or meth
On line
UserForm1.TextBox20 = Application.WorksheetFunction.DateDif(MyBDate, Date,
"y")



What to do (wringing hands here)...

 

[JMay]

STOP THE PRESSES !!!

Googling enough - I've determined that my problem is likely the DATEDIF (one
"F")
is not supported in VBA (in conjunction with Worksheetfunction..) There is
however a DATEDIFF (2 "F's") Soooooo

Private Sub TextBox19_AfterUpdate()
MyBDate = CDate(TextBox19)
If DateSerial(Year(Date), Month(MyBDate), Day(MyBDate)) <= Date Then
Me.TextBox20 = Abs(DateDiff("yyyy", Date, MyBDate))
Else
Me.TextBox20 = Abs(DateDiff("yyyy", Date, MyBDate) + 1)
End If
End Sub

THIS IS WORKING GREAT !!!

 

[Dave Peterson]

I didn't test the guts of your code--just the part that needed to check for a
date.

You still may want to check to see if you're working with a date with IsDate()
and if you really wanted to use the worksheet function (=datedif()), remember
you can use application.evaluate().