Excel Sheet Altering Date Format from UserForm

Hi,

Little embarrassed to be asking this considering the amount of returns a quick Google search on the subject brings up; but they don't work for me so I'm completely stuck.

I've got a userForm that records a date. It's just a textBox which holds the caption dd/mm/yyyy when opened. The problem as ever is that when the information is submitted and entered to a table in excel, it becomes mm/dd/yyyy

I've checked my system and the format is dd/mm/yyyy.

I've tried to format the textbox value using

Please Login or Register  to view this content.

but this didnt help.

I've formatted the cells in the excel itself to "dd/mm/yyyy", but this made no difference.

I've also tried to dictate the format of the receiving cell by using

Please Login or Register  to view this content.

but this made things worse - the practice date of 01/04/2015 (1st Apr 2015) appeared on the excel sheet as 15/03/2015

Can anyone suggest a when of getting this to work?

Thanks

Hi inq80,

You shouldn't be embarrassed. Excel date and time conversion is not trivial, even if you know the tricks.

To proceed you have to understand the following:

a. (UserForm) TextBoxes contain text only. They don't care if the contents is a date, a number, text, or a combination. It is our responsibility to extract the text and make sense of what is in the TextBox. When entering a date in a TextBox you can use any format you want, as long as you know what to expect when reading the TextBox contents.

b. VBA date functions tend to be biased in favor of the United States. Relying on the system setting for the proper date format (US or European) can be troublesome at best. When using VBA, dates should be converted using a format that VBA can't miisinterpret.

c. Excel misinterpreted your CDate() call.

Try something like the following (tested and working in the US to display dd/mm/yyyy):

Please Login or Register  to view this content.

I hope this helps.

Lewis

Hi Lewis,

Thanks for the reply - that's a lot of coding and I can just about follow it; i thought the issue might be that excel was unable to tell what I'd input into the textbox so comes to its own conclusions. To be fair though, excel has no way of telling that 01/04 is the first of Apr as its the same for Jan 4th.

I just been reading up on seeing it there was a way of forcing excel into recognizing the text in the textBox as a UK format but had no luck.

I'd love to use your reply but i would be completely stuck if it went wrong so I've attached a sheet with the example i described to see if a different solution can found which I could follow...

seem to have got round it by using

Please Login or Register  to view this content.

instead of

Please Login or Register  to view this content.

Your way works fine.

For additional information (future reference) see the attached workbook. Single Step through items in the debugger (f8) to see how the code works.

UserForm module:

Please Login or Register  to view this content.

Module1:

Please Login or Register  to view this content.

The following 'Debugger Secrets' may also help you:
a. Press 'F8' to single step (goes into subroutines and functions).
b. Press SHIFT 'F8' to single step OVER subroutines and functions.
c. Press CTRL 'F8' to stop at the line where the cursor is.
d. 'Left Click' the margin to the left of a line to set (or clear) a BREAKPOINT.
e. Press CTRL 'G' to open the IMMEDIATE WINDOW. 'debug.print' statements send their
output to the IMMEDIATE WINDOW.
f. Select View > Locals to see all variables while debugging.
g. To automatically set a BREAKPOINT at a certain location put in the line:
'Debug.Assert False'
h. To conditionally set a BREAKPOINT at a certain location put in lines similar to:
if i >= 20 and xTV20 > 99.56 then
Debug.Assert False
endif
i. A variable value will be displayed by putting the cursor over the variable name.

Lewis