Using < and > to compare date values, check checkboxes based on outcome

[puffo]

Hi all,

Update: (Original question still below in italics, title changed to reflect the direction change!) - have solved the problem with dates switching between formats when transferred to /from spreadsheet and userform. It seems whichever format I used in one, it would change formats when transferred to the other, both ways. Code below has been changed to get around this (changes shown in red)
Now the problem I am left with is date comparisons, using if, Then, > and < to compare dates and check or uncheck checkboxes based on the outcome. Do I have to change dates to a string or to a value of some sort before using the < or > ?
At the moment whatever the value of TxtPickupDate, "ChkInTransit" is checked as True, and whatever the date value in "TxtDelDate", once the cursor goes into the next box (CmbDriver), "ChkCompleted" and "ChkDelivered" become checked as True.

I have commented out the popup dialigue box because it was coming up much of the time regardless of whether delivery date was before or after the pickup date. (It should be triggered if the delivery date is before the pickup date!)

What am I doing wrong?! Thanks!

I'm sure this has been dealt with, but I can't find specifics..
I have a userform for entering data into a spreadsheet, new to vba but doing ok so far.. ish!
But.. have come across an issue transferring date in the userform (format d/mm/yyyy) to the spreadsheet. 6/8/14 in the userform comes out as 8/6/14 in the spreadsheet.
regional settings are set at australian format - d/mm/yyy
Cells in the spreadsheet where the date lands are formatted to date - dd/mm/yyyy*

I have a button that transfers info from the form to the sheet, and I'm sure it was working (in terms of the format) until I started using an "if" "then" sub to compare dates and check boxes based on the result in another part of the userform.. though I could be wrong.

Can anyone help pls? It's driving me nuts! 4 different dates on the userform and it's happening with all four of them.

relevant code (though there may be more - let me know!) - these are the only references I have to the date as far as I know..
Form starts with current date in TxtDate textbox.
Then there's a pickup date (TxtPickupDate) and then the cursor goes to delivery date (TxtDelDate), then to Driver name (CmbDriver)

Problem 1 is the date transferring in wrong format when I hit CmdEnterDetails.
Problem 2 is in the comparison of dates when cursor enters TxtDelDate and the the next box - CmbDriver.. the subroutine when entering CmbDelDate doesn't think that 27/7/14 is before 3/8/14 and the subroutine when entering CmbDriver thinks that 10/8/14 is before 5/8/14. Sometimes these subroutines work, sometimes not. I believe the problems are related...

Any help appreciated - I've been learning as I go, cutting, pasting, altering, trying....! Thanks!

**Addition** If i enter the code in US format mm/dd/yyy, it comes out in Australian dd/mm/yyyy format when sent to the spreadsheet via the CmdEnterDetails button **

Please Login or Register  to view this content.

[puffo]

There's probably some of the
TxtDate.Value = Format(TxtDate.Value, "d/mm/yyyy")
Stuff that i don't need still in there too.. it didn't seem to make a different whether I had this on entry to the txtBox or not. It's commented out on the TxtPickupDate_enter subroutine from when I was testing to see if it made any difference having it there.

Aaaany help at all appreciated..

[puffo]

Update - my fix above only worked if there is a value entered into the date boxes. If it is left blank, there is a type mismatch error... I shall leave the above so things don't get even more confusing, and search for the last part of the date transfer solution in other threads.. The above problem still needs a solution! Thanks!