Set Date to Last Day of Month

[Ron]

Hello all,

I'm working on a project that is a little over my head and appreciate
any assistance available. I'm editing code for a PeopleSoft form and
want to capture the last day of the month. The code is currently
capturing the current date. I think I've isolated the lines that set
the date. Can anyone assist with code for the last day of the month?
Thank you for your assistance, Ron

If Workbooks(CurWrkBook).DialogSheets(OptionSheet).EditBoxes
("DefaultDate").Text = "" Then
HdrDlg.EditBoxes(3).Text = Format(Date, "General Date")
HdrDlg.EditBoxes("Edit Box EffDate").Text = Format(Date,
"General Date")
Else
HdrDlg.EditBoxes(3).Text = Workbooks(CurWrkBook).DialogSheets
(OptionSheet).EditBoxes("DefaultDate").Text
HdrDlg.EditBoxes("Edit Box EffDate").Text = Workbooks
(CurWrkBook).DialogSheets(OptionSheet).EditBoxes("DefaultDate").Text
End If

 

[ker_01]

Ron-

Where you currently have Date, try:
cdate((month(date())+1)& "/" & year(date()))-1

This takes the current month and adds one (so it evaluates to the first day
of next month), then subtracting one at the end subtracts one day, leaving
you with the last date of the current month, regardless of whether it has 28,
29 (leap year), 30, or 31 days.

HTH,
Keith

 

[Ron]

Hi Keith, worked fantastic. One quick one. These dates flow to cells
on the spreadsheet. I have a validation process that loops through
the sheet validating different formats. How would I include the date
cells? I just want to validate that the date year is the current year
or the next year i.e. 2009 or 2010. Thanks again your code is greatly
appreciated, Ron

 

[Ron]

Hi Keith, thank you for your excellent resolution. Ron