Userform extracting dates from my spreadsheet changes format from uk settings to american

I am new to VBA and have tried to keep things simple.

I have a user form that extracts data from a "date" fields formatted in dd/mmm/yy, no matter what I try the data is extracted as mm/dd/yy. Therefor when I update the spreadsheet the wrong date is sent to my data table. It is totally corrupting my data table. The user forms are quite basic.
Danny McLeodAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ejgil HedegaardCommented:
Probably the dates are text, and not real dates.
Real dates are numbers.

Check the cell format.
If it is General but it looks like a date, the date is a text.

When VBA reads the cell value into a date variable, the variable will be the date (number), even if the cell value was a text that looks like a date.
If you then write it back to the cell (it is a number) and it was a number before formatted as UK dates, the format should be as it was.

If the dates in the table are text, and you write the date (number) to the cell, Excel know it is a date and "helps you" setting the format to date.
If you have US setting on your machine it is shown in US format.
You could change the format to UK dates, but I guess you want it to remain as text.
Then you have to set the cell format to text before writing the value to the cell.
The code for text format looks like this: Range("A1").NumberFormat ="@"
Then write the date as text to the cell, formatted to look as a date, using
Range("A1") = WorksheetFunction.Text(xx, "dd/mmm/yy"), where xx is the date variable in VBA.
If you don't change the cell format to text, Excel "help" and see that the text is a date, and translate it to a date (number) formatted to your date setting.

It generally helps to see the data and the code, so you could also upload a file.
Danny McLeodAuthor Commented:
I have attached a sample of what is happening with my date issue. When you select a name in the "name " field, the DOB appears as it should in the "DOB" field. When I hit the "Update" command it send the data to the list BUT in the alternative American format. if I leave things alone and hit update again it just toggles the date between the 2 date formats. The fields are set to "custom "dd mmm yy". it driving me crazy.
Ejgil HedegaardCommented:
It can be difficult to work with dates in VBA.

The values in a textbox can only be text, not numbers, and a date is a number, so to get it correct the date number must be converted to a text before used in the textbox.
VBA recognise the value as a date, and "helps" converting the date to a text, unfortunately wrong to US date format.
VBA is developed in US, and probably the developers did not think of using the local setting.
Defining txtdob as Date, with the same name as the textbox does not help, since a textbox can not be a date type.
txtdob is just another variable, and has nothing to do with the textbox object txtdob.
Using the same name can confuse where a value is assigned to.

To get the date into the textbox, this is used
Me.txtdob = WorksheetFunction.Text(ws.Cells(x, "B"), "dd/mm/yyyy")
Here VBA is told to convert the date (number) to a specific text format.

It is also tricky to get the textbox value (date as text) back to a real date to write to the sheet.
The conversion function CDate from text to dates typically works correct with local date format, but not always, as it will accept the "date" 10/15/2015, and think it is US format, with the result 15. October 2015.
Instead I have used the function DateSerial(Year, Month, Day) inserting the parts of the text date.
DateSerial has the problem that month and days are not limited to real dates.
The "date" 10/15/2015 will be translated to 10-03-2016, similar to Excels Date function.
This is often practical since Date(2015, 10, 0) will be last day of September, but here it is a problem.
So there is a check that the Day, Month and Year of the textbox string match the same in the converted date.

I have changed hide and reset of the input values of the userform to Unload.
Since no values are kept for next show, Unload and new Show clear all values.

Added "Option Explicit" at the top to require variable declaration.
You should always use that, go to Tools-Options and mark "Require variable declaration"
It avoids misspelling that leads to a new variable, since the procedures can't be compiled.
Helps in coding, type a few characters and use Ctrl+Space to insert the name or get a list of names.

Moved the generally used variables to above the subs, and inserted UserForm_Initialize to set the common used variables at open.
Changed code to avoid Exit Sub.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Danny McLeodAuthor Commented:
Awesome, I have been trying for weeks to sort this date issue out. I am over the moon with your solution. Thank you very much Ejgil.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.