Date validation teaser!!!

[adambc]

I have a Workbook which records requests received, which is periodically updated with progress - NB it is not used in lieu of Project Management/Project Tracking tools; it is there to provide the Executive Team in the charity I’m volunteering at (in retirement!) an oversight …

It works using two UserForms …

frmNewRequest … used to add new requests - there are a number of Command Button/Text Box combinations where the Command Button calls a Date Picker and writes the date selected to the Text Box - behind this I have written a simple VBA date validation routine that is triggered by a Text Box Change to ensure that the date selected is not before today

frmUpdateRequest … is essentially the same form except that it loads the data into the form from the underlying Worksheet record - I still need the aforementioned Command Button/Text Box combinations including the validation, but the problem occurs when a date previously entered has now past - when the form loads the data (into the Text Box), the validation kicks in, which I don’t want to happen to allow reporting on dates “missed”

Can anyone help eg is there a way I can distinguish between a Text Box Change as a result of the Command Button/Date Picker, from a Text Box Change as a result of loading an existing record?

Thanks for any help you can give me …

 

[Micron]

Don't use the Change event? Not sure what would be more suitable for your use re date validation but I suspect BeforeUpdate. Consider doubleclick event on date fields and eliminate the buttons?

 

[adambc]

Don't use the Change event? Not sure what would be more suitable for your use re date validation but I suspect BeforeUpdate. Consider doubleclick event on date fields and eliminate the buttons?

@Micron

How will BeforeUpdate help? Surely the validation will still trap updates as a result of loading data rather than “manual” input?

And how would a doubleclick event vs. a Command Button help?

 

[adambc]

I have a Workbook which records requests received, which is periodically updated with progress - NB it is not used in lieu of Project Management/Project Tracking tools; it is there to provide the Executive Team in the charity I’m volunteering at (in retirement!) an oversight …

It works using two UserForms …

frmNewRequest … used to add new requests - there are a number of Command Button/Text Box combinations where the Command Button calls a Date Picker and writes the date selected to the Text Box - behind this I have written a simple VBA date validation routine that is triggered by a Text Box Change to ensure that the date selected is not before today

frmUpdateRequest … is essentially the same form except that it loads the data into the form from the underlying Worksheet record - I still need the aforementioned Command Button/Text Box combinations including the validation, but the problem occurs when a date previously entered has now past - when the form loads the data (into the Text Box), the validation kicks in, which I don’t want to happen to allow reporting on dates “missed”

Can anyone help eg is there a way I can distinguish between a Text Box Change as a result of the Command Button/Date Picker, from a Text Box Change as a result of loading an existing record?

Thanks for any help you can give me …

I have solved the teaser myself!!!

I’ve put the validation in the Command Button sub before the date selected gets anywhere near the Text Box - as a result, when I load a date in the past into the Text Box no validation takes place …

Simple when you think about it!!!

 

[Micron]

How will BeforeUpdate help? Surely the validation will still trap updates as a result of loading data rather than “manual” input?

If data doesn't pass validation, you cancel the event - no update, but that's about validation. Your problem was that the change event was being triggered by the userform initialization event, yes? You solved your problem the same way - you just moved validation to a button click event. Usually, validation is done before the data is accepted in the control, not after.