Data validation of dates entered using Calendar Control not working

[KateV]

Hi everyone,

Been stuck on this date validation issue for about a week now, browsing online through forums and I've not been able to find a solution so hopefully someone out there can help me.

I’m a first time VBA coder (and am slowly making my way through my newly purchased VBA for Dummies book), so please be kind!

Basically, I’ve created a form (not a userform) in an Excel worksheet (Excel 2010) by using the ActiveX Controls and through much trial and error have managed to populate a couple of text boxes by using calendar control 8.0 - I tried using the datetimepicker but had some issues with the calendar appearing in 2 places on the worksheet and screwing up the formatting of the text box.

The problem I have is when I try to validate the dates against each other.
Here’s the scenario: I have an on-sale date and a price revert date and I need to make sure that the date that has been entered into the “price revert” date falls after the “on-sale” date – it doesn’t matter how long after, just as long as it is >= the on-sale.

My current code is:

Moderator's note: Please take the time to review our rules. There aren't many, and they are all important. Rule #3 requires code tags. I have added them for you this time because you are a new member. --6StringJazzer

Please Login or Register  to view this content.

Where RevDate refers to the Price Revert textbox and OSDate refers to the On-sale textbox.
I encounter the following issues with the above code
1. ANY date I enter into the Price Revert textbox results in the msgbox appearing regardless of whether or not it is before the On-sale date.
2. Even though the msgbox appears, it still allows me to enter an invalid date and move through the form.
3. Once I’ve entered an invalid date, if I re-select another invalid date (for testing purposes), the msgbox does not appear.

It seems a little contradictory, with the msgbox appearing and not appearing but I assure you I’ve encountered each of these issues.
I’ve tried ignoring the textboxes completely and running the validation check against their respective linked cells in Sheet2 but to no avail. If it helps to attach the actual form, let me know.

What I would like to do is for the “Invalid Date” message to appear only if the user selects a date that is before the on-sale.

Also, just as an aside, would it be best for me to validate the dates in the calendar control or the text box control?

Apologies if I sound like a complete moron, but I’m trying to teach myself. It appears I’m not a very good teacher.

Thanks in advance for any help you can provide.

[AlphaFrog]

RevDate.Value and OSDate.Value are both text strings and not date values as both controls are textboxes. If you compare the two values directly, you are comparing text and not serial date values.

Try something like this to convert the text-dates to serial-dates and then compare...
If CDate(RevDate.Value) <= CDate(OSDate.Value) Then

After you show the invalid date message, clear the textbox e.g; OSDate.Value = ""

It would be better\easier in my mind to test the dates from a calendar control event procedure.

If you need specific help, it would be best to post an example workbook though I have an older version of Excel. I cannot load some of the newer versions of the ActiveX controls.

[KateV]

Thanks for the quick response AlphaFrog. I tried your suggestion but I kept getting a runtime error.

I have attached for you a copy of my form:

Price New Line Advice FormV1 - New CalCopy.xlsm

I had previously tried formatting the text boxes in "dd/mm/yyyy" format but for some reason, any date selected preceding the current date formats as "mm/dd/yyyy".

I'm not sure what else I need to be doing.

[KateV]

Further to the above, It appears that anytime I select a date in which the day is a single digit ie. 2nd March 2014, the format changes to mm/dd/yyyy so instead of being displayed as 02/03/2014 it is displayed as 03/02/2014 - using my current code

[KateV]

Figured it out after concentrating a little more.
Your assistance was much appreciated though!

My new code (It does what I need it to, I just need to accept that the dates will be in US format rather than Aus):

Please Login or Register  to view this content.

[AlphaFrog]

I had been working on it. This is how I would do it for what it's worth. It uses only one Calendar control called DateCal and it positions it next to each textbox as needed. Ausi date format (dd/mm/yyyy) is OK.

Please Login or Register  to view this content.

[KateV]

Well your code works much better! and aesthetically the form looks better.
I've modified so that it uses command buttons to call the calendar rather than double clicking on the textbox.

Thank you so much for taking the time to look at this for me. I really do appreciate your help!