Hi all,
I have a userform with two textboxes - one to enter a start date. The other
shows the calculated end date from textbox1 change event.
However, my code is not producing the required result (entered date + 12
months) I would be grateful if some kind person could assist and correct the
problem please.

Private Sub HolStartTextBox_Change()
Dim startdate As Date, enddate As Date
On Error Resume Next
startdate = CDate(HolStartTextBox.Text)
enddate = Year(startdate) & "/" & Month(startdate) + 12 & "/" & Day(0)
If IsDate(startdate) = True Then
HolEndTextBox.Text = Format(enddate, "dd/mm/yyyy")
End If
On Error GoTo 0
End Sub

--
Jon

make enddate 365 days after the start date

enddate = startdate + 365

"johnboy" wrote:

> Hi all,
> I have a userform with two textboxes - one to enter a start date. The other
> shows the calculated end date from textbox1 change event.
> However, my code is not producing the required result (entered date + 12
> months) I would be grateful if some kind person could assist and correct the
> problem please.
>
> Private Sub HolStartTextBox_Change()
> Dim startdate As Date, enddate As Date
> On Error Resume Next
> startdate = CDate(HolStartTextBox.Text)
> enddate = Year(startdate) & "/" & Month(startdate) + 12 & "/" & Day(0)
> If IsDate(startdate) = True Then
> HolEndTextBox.Text = Format(enddate, "dd/mm/yyyy")
> End If
> On Error GoTo 0
> End Sub
>
> --
> Jon

Joel,
I need to show end date as 12 months including the last day of the 12 month
eg:
start 01/01/2007 end 31/12/2007
I had thought about your approach but also got some strange results - eg:
start 01/04/2007 end date shows 30/03/2008? (based on 364 days) not
31/03/2008 as I would expect.
--
JB


"Joel" wrote:

> make enddate 365 days after the start date
>
> enddate = startdate + 365
>
> "johnboy" wrote:
>
> > Hi all,
> > I have a userform with two textboxes - one to enter a start date. The other
> > shows the calculated end date from textbox1 change event.
> > However, my code is not producing the required result (entered date + 12
> > months) I would be grateful if some kind person could assist and correct the
> > problem please.
> >
> > Private Sub HolStartTextBox_Change()
> > Dim startdate As Date, enddate As Date
> > On Error Resume Next
> > startdate = CDate(HolStartTextBox.Text)
> > enddate = Year(startdate) & "/" & Month(startdate) + 12 & "/" & Day(0)
> > If IsDate(startdate) = True Then
> > HolEndTextBox.Text = Format(enddate, "dd/mm/yyyy")
> > End If
> > On Error GoTo 0
> > End Sub
> >
> > --
> > Jon

Use dataserial. Specifying the day as 0 gives you the last day of the
previous month

enddate = DateSerial(Year(startdate) + 1, Month(startdate) + 1, 0)


"johnboy" wrote:

> Joel,
> I need to show end date as 12 months including the last day of the 12 month
> eg:
> start 01/01/2007 end 31/12/2007
> I had thought about your approach but also got some strange results - eg:
> start 01/04/2007 end date shows 30/03/2008? (based on 364 days) not
> 31/03/2008 as I would expect.
> --
> JB
>
>
> "Joel" wrote:
>
> > make enddate 365 days after the start date
> >
> > enddate = startdate + 365
> >
> > "johnboy" wrote:
> >
> > > Hi all,
> > > I have a userform with two textboxes - one to enter a start date. The other
> > > shows the calculated end date from textbox1 change event.
> > > However, my code is not producing the required result (entered date + 12
> > > months) I would be grateful if some kind person could assist and correct the
> > > problem please.
> > >
> > > Private Sub HolStartTextBox_Change()
> > > Dim startdate As Date, enddate As Date
> > > On Error Resume Next
> > > startdate = CDate(HolStartTextBox.Text)
> > > enddate = Year(startdate) & "/" & Month(startdate) + 12 & "/" & Day(0)
> > > If IsDate(startdate) = True Then
> > > HolEndTextBox.Text = Format(enddate, "dd/mm/yyyy")
> > > End If
> > > On Error GoTo 0
> > > End Sub
> > >
> > > --
> > > Jon

Joel,
that solved it!
many thanks
--
JB


"Joel" wrote:

> Use dataserial. Specifying the day as 0 gives you the last day of the
> previous month
>
> enddate = DateSerial(Year(startdate) + 1, Month(startdate) + 1, 0)
>
>
> "johnboy" wrote:
>
> > Joel,
> > I need to show end date as 12 months including the last day of the 12 month
> > eg:
> > start 01/01/2007 end 31/12/2007
> > I had thought about your approach but also got some strange results - eg:
> > start 01/04/2007 end date shows 30/03/2008? (based on 364 days) not
> > 31/03/2008 as I would expect.
> > --
> > JB
> >
> >
> > "Joel" wrote:
> >
> > > make enddate 365 days after the start date
> > >
> > > enddate = startdate + 365
> > >
> > > "johnboy" wrote:
> > >
> > > > Hi all,
> > > > I have a userform with two textboxes - one to enter a start date. The other
> > > > shows the calculated end date from textbox1 change event.
> > > > However, my code is not producing the required result (entered date + 12
> > > > months) I would be grateful if some kind person could assist and correct the
> > > > problem please.
> > > >
> > > > Private Sub HolStartTextBox_Change()
> > > > Dim startdate As Date, enddate As Date
> > > > On Error Resume Next
> > > > startdate = CDate(HolStartTextBox.Text)
> > > > enddate = Year(startdate) & "/" & Month(startdate) + 12 & "/" & Day(0)
> > > > If IsDate(startdate) = True Then
> > > > HolEndTextBox.Text = Format(enddate, "dd/mm/yyyy")
> > > > End If
> > > > On Error GoTo 0
> > > > End Sub
> > > >
> > > > --
> > > > Jon

What did you expect from day(0)?

How about a test:
msgbox day(0) & vblf & format(0,"mmmm dd, yyyy")

How about something like:

Option Explicit
Private Sub CommandButton1_Click()
Unload Me
End Sub
Private Sub TextBox1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)

Dim StartDate As Date
Dim EndDate As Date

Me.Label1.Caption = ""
If IsDate(Me.TextBox1.Text) = True Then
StartDate = CDate(Me.TextBox1.Text)
EndDate = DateSerial(Year(StartDate), _
Month(StartDate) + 12, Day(StartDate))
Me.TextBox2.Text = Format(EndDate, "dd/mm/yyyy")

Else
Me.Label1.Caption = "Please Enter a date!"
Cancel = True
End If

End Sub
Private Sub UserForm_Initialize()
With Me.CommandButton1
.Cancel = True
.TakeFocusOnClick = False
.Caption = "Cancel"
End With

Me.Label1.Caption = ""
End Sub

ps. If you want to remove the possibility of using the wrong date (01/02/03 is
ambiguous), maybe using a calendar control to prompt the user would be better:

See Ron de Bruin's site for more info:
http://www.rondebruin.nl/calendar.htm


johnboy wrote:
>
> Hi all,
> I have a userform with two textboxes - one to enter a start date. The other
> shows the calculated end date from textbox1 change event.
> However, my code is not producing the required result (entered date + 12
> months) I would be grateful if some kind person could assist and correct the
> problem please.
>
> Private Sub HolStartTextBox_Change()
> Dim startdate As Date, enddate As Date
> On Error Resume Next
> startdate = CDate(HolStartTextBox.Text)
> enddate = Year(startdate) & "/" & Month(startdate) + 12 & "/" & Day(0)
> If IsDate(startdate) = True Then
> HolEndTextBox.Text = Format(enddate, "dd/mm/yyyy")
> End If
> On Error GoTo 0
> End Sub
>
> --
> Jon

--

Dave Peterson

Dave,
I expected Day(0) to give me last day of the month.
Anyway - thankyou for your kind suggestion but I have now resolved problem
with joel's earlier posting.
--
JB


"Dave Peterson" wrote:

> What did you expect from day(0)?
>
> How about a test:
> msgbox day(0) & vblf & format(0,"mmmm dd, yyyy")
>
> How about something like:
>
> Option Explicit
> Private Sub CommandButton1_Click()
> Unload Me
> End Sub
> Private Sub TextBox1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
>
> Dim StartDate As Date
> Dim EndDate As Date
>
> Me.Label1.Caption = ""
> If IsDate(Me.TextBox1.Text) = True Then
> StartDate = CDate(Me.TextBox1.Text)
> EndDate = DateSerial(Year(StartDate), _
> Month(StartDate) + 12, Day(StartDate))
> Me.TextBox2.Text = Format(EndDate, "dd/mm/yyyy")
>
> Else
> Me.Label1.Caption = "Please Enter a date!"
> Cancel = True
> End If
>
> End Sub
> Private Sub UserForm_Initialize()
> With Me.CommandButton1
> .Cancel = True
> .TakeFocusOnClick = False
> .Caption = "Cancel"
> End With
>
> Me.Label1.Caption = ""
> End Sub
>
> ps. If you want to remove the possibility of using the wrong date (01/02/03 is
> ambiguous), maybe using a calendar control to prompt the user would be better:
>
> See Ron de Bruin's site for more info:
> http://www.rondebruin.nl/calendar.htm
>
>
> johnboy wrote:
> >
> > Hi all,
> > I have a userform with two textboxes - one to enter a start date. The other
> > shows the calculated end date from textbox1 change event.
> > However, my code is not producing the required result (entered date + 12
> > months) I would be grateful if some kind person could assist and correct the
> > problem please.
> >
> > Private Sub HolStartTextBox_Change()
> > Dim startdate As Date, enddate As Date
> > On Error Resume Next
> > startdate = CDate(HolStartTextBox.Text)
> > enddate = Year(startdate) & "/" & Month(startdate) + 12 & "/" & Day(0)
> > If IsDate(startdate) = True Then
> > HolEndTextBox.Text = Format(enddate, "dd/mm/yyyy")
> > End If
> > On Error GoTo 0
> > End Sub
> >
> > --
> > Jon
>
> --
>
> Dave Peterson
>