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.

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.

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!