Date Format changes from Userform to Spreadsheet

Hi All,

Attached is a test code example of the problem I am encountering. I have looked at the numerous posts with similar if not the same problem however none that i have tried including CDate has managed to correct the problem.

As with other posts I need to format the date in a dd/mm/yyyy format. When the date is selected it appears correct in the userform however when it is added to the spreadsheet it changes to US format mm/dd/yyyy. My computer's regional settings are set correctly. I am also using Excel 2010.

When the data is returned from the spreadsheet to be edited it is formatted correctly in the userform.

Strange is how the data appears on the spreadsheet. If the day is 13 or more e.g. 17/04/2014 it will be formatted correctly on the spreasheet but be formatted as text. Conversely, if the day is 12 or below e.g. 12/04/2014 (April 12) will appear as 04/12/2014 (December 4) and be formatted as a date.

I have checked the formatting of column B where the dates are contained and if I change the formatting from Date (Short Date) to General. The dates with a day of 12 or below (second example above) will change to a number e.g.4/6/1999 will become 36315 whereas the first examples remain as text.

I need the dates to be "dates" as I am calculating a current DOB.


I have tried to use DatePicker however this has not worked either for different reasons in that i am unable to capture a NULL error when reseting the userform controls and workign with a blank textbox in conjunction with the calculating the age.

In the attached example I am using Monthview. I have tried RoyUK's CalendarForm and this resulted in the same formatting error as described above.

I apologise if there is a solution i have overlooked and appreciate any assistance.

Thanks
Darren

Hello Sorjas,

I recently finished a International Date Picker UserForm. This will correctly display dates based on your regional settings. You can also chosse to copy the date to a fixed cell on the worksheet or to the active cell. The date formats can be according to what the system has available. Try it out and let me know if you can use it.

Hello Sorjas,

Under File on the Ribbon...

1. Click Options
2. Click Trust Center
3. Click the button Trust Center Options...
4. Click Macro Settings
5. Click the check box Trust access to the VBA project object model. The box should show a check. If not, click it again.

Leith,

Nice job on the Calendar Form as always, and thank you for sharing. Since I am using Excel 2003 and Vista, I had to make a few changes to make it run:
a. Added 32 bit CONDITIONAL COMPILATION in Module 'API_Timer'.
b. GetShortTime() in Module 'CalendarAPI' gets the time format using registry item 'sShortTime' which in Vista (and probably XP) the item name is 'sTimeFormat'. I modified the code as follows:

Please Login or Register  to view this content.

c. I added Module 'ModLJM' which contains function LjmGetOperatingSystemVersion(). You probably have to add CONDITIONAL COMPILATION to get it to compile in 64 bit Excel. I was unable to find a 64 bit reference to the 64 bit code for the GetVersionExA() Api and it's user deifned data structure:

Please Login or Register  to view this content.

Additionally, I got a runtime error in the code, after changing the date cell location. It seems that when changing the date cell location the Calendar Form goes from modal to non-modal. After the location is changed, the Calendar Form appears to still be non-modal. It the 'Show Calendar' Command Button is selected, the run time error occurs (Error 400 = Form already displayed - Can't show modally).

Lewis

FYI you only need to add PtrSafe for 64 bit compatibility of GetVersionEx.

Hello Lewis,

Thank you for taking the time to add the code for the different Office versions. I was aware of the registry problem but did not know the key name or names for 2007 and Vista. Thanks for updating that piece also. Looks like I need to release a version 4 of this.

Any feedback from users is welcome.

Hello Sorjas,

I don't know what to tell you. The API code in the workbook I posted, taps into the regional and language settings of your system.

Recheck your Region and Language settings. If the problem is not there then it could be a problem with tying into the code I wrote. If that is the case, you should post the most current copy of your workbook so I can review it.

Hi Darren,

Your existing code is fine, you just have to understand it. It is written to be efficient, not to be understood and maintained. For example all the TextBoxes in the UserForm have a generic name of 'Reg1' through 'Reg13'.

When the data is saved, it looks like in Sub 'cmdAdd_Click()':

Please Login or Register  to view this content.

If it were my project, I would have named the textboxes to be more descriptive, e.g. 'TextBoxFirstName', 'TextBoxDateOfBirth', etc. This is just my programming style. Each person and/or organization has their own style (and/or standards). When I have to look at the code in the future, I won't have any idea what a 'Reg5' is, but 'Date Of Birth' will have meaning to me.

I would change the code as follows:

Please Login or Register  to view this content.

I would move the code that adds the data to an ordinary code module such as 'Module1':

Please Login or Register  to view this content.

I prefer the Ordinary Code Module because I think:
a. Ordinary Code modules are easier to debug than UserForm modules.
b. The routines can be tested semi-independently of the UserForm (if during debugging the UserForm is Non-Modal [i.e. UserForm1.Show False]). Non-Modal UserForms allow access to the spreadsheet while the UserForm is active. Data Can be put in the UserForm, and then in the debugger (or from the Spreadsheet by calling the Macro or from a Temporary Command Button) the routine can be tested.

My code is a lot longer, runs slower (but in this situation run time is NOT an issue), but it is a lot easier for me to understand and debug.

I hope this helps.

Lewis

Hello Sorjas,

As I mentioned in the last post, if the regional settings are correct then you should post a copy of the most recent workbook for review.

Hello Sorjas,

Okay, the UserForm works correctly. The problem is with the date format listing. Since both the day and month are 1, you can not tell which is which when they are switched. So, I am working on correcting that.

Hello Sorjas,

Try this version of the workbook. I have added a legend to the "Date" tab that shows the day, month, and year used in the date format. This should fix the problem.

Hello Sorjas,

I have uploaded the file again. This time it should be correct. Not sure what the problem was with the last upload. The file on my computer was correct, yet the file that was upload was not. It must have been PFM.

Check this workbook out and let me know the results.

Rather than

Please Login or Register  to view this content.

each line should start with just

Please Login or Register  to view this content.

since nextrow is a Range object.

Duplicate post removed.