Date Calculation in UserForm TextBox
 

I am making a form to receive certain loan parameters and then
calculate certain other parameters. According to our rules, if
the close date is 12/02/2003-01/01/2004, the First Payment Date
is on 02/01/2004, and so on for every month. For some reason,
the first day of each month is an odd-ball, but so be it.
I came up with a spreadsheet formula that gives the correct
first payment date for all close dates.
=IF(DAY(B16)=1,DATE(YEAR(B16),(MONTH(B16)+1),(DAY( B16)-DAY(B16)+1)),
DATE(YEAR(B16),(MONTH(B16)+2), (DAY(B16)-DAY(B16)+1)))
where B16 is any close date.
What I need to do is convert this into VBA code for a text box
value after the close date is entered in another text box.
At present, that is accomplished by the following.

Public Sub UserForm_Activate()
txtCloseDate.Value = Format(Now(), "mmmm d, yyyy")
Any help would be appreciated.

Date Calculation in UserForm TextBox
 

Public Function FirstPayment(sStr)
Dim dt as Date
Dim dt1 as Variant
if isdate(sStr) then
dt = cDate(sStr)
if day(date) = 1 then
dt1 = DateSerial(year(dt),Month(dt)+1,Day(dt)-day(dt)+1)
else
dt1 = DateSerial(year(dt),month(dt)+2,Day(dt)-day(dt)+1)
end if
else
dt1 = "Invalid Date"
end if
FirstPayment = dt1
End Function

But unless I am missing something Day(dt)-Day(dt)+1 is equal to 1

Public Function FirstPayment(sStr)
Dim dt as Date
Dim dt1 as Variant
if isdate(sStr) then
dt = cDate(sStr)
if day(date) = 1 then
dt1 = DateSerial(year(dt),Month(dt)+1,1)
else
dt1 = DateSerial(year(dt),month(dt)+2,1)
end if
else
dt1 = "Invalid Date"
end if
FirstPayment = dt1
End Function

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim dt as Variant
dt = FirstPayment(txtCloseDate.Value)
if dt < "Invalid Date" then
txtFirstPaymentDate = Format(dt, "mmmm d, yyyy")
else
msgbox "Bad Date"
cancel = True
end if
End Sub

--
Regards,
Tom Ogilvy

"John Pierce" wrote in message
om...
I am making a form to receive certain loan parameters and then
calculate certain other parameters. According to our rules, if
the close date is 12/02/2003-01/01/2004, the First Payment Date
is on 02/01/2004, and so on for every month. For some reason,
the first day of each month is an odd-ball, but so be it.
I came up with a spreadsheet formula that gives the correct
first payment date for all close dates.
=IF(DAY(B16)=1,DATE(YEAR(B16),(MONTH(B16)+1),(DAY( B16)-DAY(B16)+1)),
DATE(YEAR(B16),(MONTH(B16)+2), (DAY(B16)-DAY(B16)+1)))
where B16 is any close date.
What I need to do is convert this into VBA code for a text box
value after the close date is entered in another text box.
At present, that is accomplished by the following.

Public Sub UserForm_Activate()
txtCloseDate.Value = Format(Now(), "mmmm d, yyyy")
Any help would be appreciated.

Date Calculation in UserForm TextBox
 

Tom,
Your code worked perfectly. Thanks very much. And you were correct about
the formula, the simpler version is adequate.

John

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!