BillySilly
New Member
- Joined
- Jan 23, 2013
- Messages
- 49
Hi
I have a worksheet that has a cell for the user to provide a StartDate. Formulae use the contents of this cell L5 to populate 60 cells on the sheet with dates and days.
This all works fine if the user enters a valid StartDate, but if the user wilfully or accidentally enters an invalid date the sheet is populated with errors.
My first thought was to use data validation in the StartDate cell, setting it to Date with 01/01/2013 as the start date and 31/12/30 as the end date. This works well if the date is invalid. It still fills the sheet with errors but gives a message box asking the user for a valid date with the Error Alert “The date must be a valid date in the form dd/mm/yy and the dd must be <=28”. Excel must therefore contain a buit-in validation routine but I don’t know what it is called or if it can be used in coding. Also you can see that any valid date will pass and I cannot see how to trap dates with Day>28 (or reduce any which are to 28) which is there to avoid roll-over problems when the Month is incremented.
Can anyone help with this?
My second thought was to write a macro called by activating the cell but I am having difficulty with using InputBox for dates as the Date functions in Excel97 don’t seem to work in a macro – I cannot assume that the user has the ToolPack installed.
Has anyone got code for InputBox for dates?
I have a worksheet that has a cell for the user to provide a StartDate. Formulae use the contents of this cell L5 to populate 60 cells on the sheet with dates and days.
This all works fine if the user enters a valid StartDate, but if the user wilfully or accidentally enters an invalid date the sheet is populated with errors.
My first thought was to use data validation in the StartDate cell, setting it to Date with 01/01/2013 as the start date and 31/12/30 as the end date. This works well if the date is invalid. It still fills the sheet with errors but gives a message box asking the user for a valid date with the Error Alert “The date must be a valid date in the form dd/mm/yy and the dd must be <=28”. Excel must therefore contain a buit-in validation routine but I don’t know what it is called or if it can be used in coding. Also you can see that any valid date will pass and I cannot see how to trap dates with Day>28 (or reduce any which are to 28) which is there to avoid roll-over problems when the Month is incremented.
Can anyone help with this?
My second thought was to write a macro called by activating the cell but I am having difficulty with using InputBox for dates as the Date functions in Excel97 don’t seem to work in a macro – I cannot assume that the user has the ToolPack installed.
Has anyone got code for InputBox for dates?
Last edited:
