Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dates In UserForm Textboxes
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dates In UserForm Textboxes
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dates In UserForm Textboxes
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
For Each array - TextBoxes in a UserForm | Excel Programming | |||
UserForm TextBoxes | Excel Discussion (Misc queries) | |||
userform & textboxes | Excel Programming | |||
userform textboxes again | Excel Programming | |||
userform textboxes | Excel Programming |