Data validation of dates entered using Calendar Control not working

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

Private Sub RevDate_LostFocus()
    If RevDate.Value <= OSDate.Value Then
     MsgBox "Invalid Date. Price revert date must be after On-sale date"
    End If
End Sub

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.