Excel VBA Userform Date issue

I suggest that you attempt to re-code using the Split and DateSerial functions anywhere you want to convert a Text date to an actual date and see how you go. If you want to upload examples for more assistance then I prefer that you create a English version for me to Download.

Hi Ozzie

thank you for the explanation - I think it is close now!

I think we may have crossed over on replies as I followed up to my first one as below

Ok forget that last message - even though the script was wrong i still couldnt get it to work!!!!

So Ive had a chance now to review your file and it works fine on my pc - all formats correct

I just need to fiddle around with things to get it you work on my user form -

If I add most of the script into my CMDSAVE macro will that work? if I add those lines before yje existing message box lines and call commands - will it work there? obviously the save function in my VBA Calls the "submit" script which transfer the entries to the worksheet so Ill have to work out how to do that  with out using the "With Worksheets etc part of your script -

The elapsed time calculation needs to sit hidden in the background and not visible to the user on the user form as that is not part of the job role  - so Im thinking of still adding the relevant "tmetimedifference" box exactly as you suggest But just hide the box on the user sheet then add an appropriate line in the "submit" script e.g

.Cells (Irow,xx) = etc

as well as clearing on the "reset" script

Any advice or tips on how to do that ?

Thank you so much again

Anyway I think if its possible to do the above stuff regarding adding the various parts from your example into my save script - do you think it will work? nly one way to find out

Regarding the regional formats - theres nothing on my PC which is arabic other than the unicode setting but the problem was there before I did that change anyway

However i did find out that using even regional settings that that CDate always tries to put it into US styles AND this can be tested where and this is my issue actually where the day is 12 or less - if the day is more than 12 then excel cant argue !!! as there is no 13th month - no body has a solution to this as I understand but I think we are there with how to explain and demonstrate the solution

Thanks Adrian

Adrian

My reply was to your previous message to me and did not include any reply to your last message. I was writing the reply and was interrupted with a phone call part way through so took a long time and I did not get your last message until after I posted.

See how things go with my reply before I attempt a further reply to your last message.

Hi Adrian,

In your example you have dimensioned Calldate as a date.  When you use Me you are referring to the Userform and you can't have the dimensioned variable attached to a userform. Controls (like TextBoxes) are attached to Userforms.

Also you cannot reformat the date inside the date variable. You only use the Format to change the date display when outputting the date as text. Therefore the example you posted will not work. The value in Me.txtDate is already in text format so Format is not used to convert an already text value.

I believe that you main problem now is with the date when it is written back to the worksheet, I suggest that for some reason CDate is not recognizing your default date format as d/m/y and it thinks it is m/d/y. Therefore, I suggest that you study the code in the example workbook I provided in my last post. Use the Split function to assign the textbox values (Day, Month and Year) to an array and then from the array pick out the Year, Month and Day to use with DateSerial to create a valid date variable. 

Explanation of the following line of code where Me.txtStartDate is in d/m/y format:

arrSplit = Split(Me.txtStartDate, "/")

The TextBox value is divided into 3 components which are delimited with slashes and assigns each component to the elements of the array as follows. (Note: It is a zero based array)

Day assigned to arrSplit(0)

Month assigned to arrSplit(1)

Year assigned to arrSplit(2)

Then using DateSerial as follows it assigns the combined values to a Date Variable. Function format: DateSerial(Year, Month, Day)

dteStartDate = DateSerial(arrSplit(2), arrSplit(1), arrSplit(0))

The above method does not rely on the default date format. It specifically identifies which component of the text date to assign as the Year, Month and Day to create the correct value for the date variable.

My interpretation of your previous explanation is that even when the dates displayed in the desired Numberformat of d/m/y in the worksheet cells display as m/d/y format in the Formula Bar and you have to edit them in m/d/y format. Is my interpretation correct? If correct, it supports my theory that maybe Excel is interpreting your default date system as m/d/y and I am guessing that it could be related to your combined use of use of Arabic and your date format but I can't be certain that is the reason.

Just for interest, my very first lecturer instilled in us that if we submit assignments that use defaults when it is possible to program without using defaults then do not expect to pass the assignments. I guess that is where my objection to CDate originated because when used with Dates it defaults to the computers setup for the regional date format. When used with Times it is OK because AFAIK there is only one time format of hms even if different delimiters are used. DateSerial does not rely on the default date format for the computer.

Anyway try what I have suggested and I believe that it should alleviate the problem of writing the date from the TextBox to the worksheet. However, I cannot help with the date format in the cells being in d/m/y format and appearing as m/d/y format in the Formula bar because that appears to be an idiosyncrasy (or bug) with Excel.

Ok forget that last message - even though the script was wrong i still couldnt get it to work!!!!

So Ive had a chance now to review your file and it works fine on my pc - all formats correct

I just need to fiddle around with things to get it you work on my user form -

If I add most of the script into my CMDSAVE macro will that work? if I add those lines before yje existing message box lines and call commands - will it work there? obviously the save function in my VBA Calls the "submit" script which transfer the entries to the worksheet so Ill have to work out how to do that  with out using the "With Worksheets etc part of your script -

The elapsed time calculation needs to sit hidden in the background and not visible to the user on the user form as that is not part of the job role  - so Im thinking of still adding the relevant "tmetimedifference" box exactly as you suggest But just hide the box on the user sheet then add an appropriate line in the "submit" script e.g

.Cells (Irow,xx) = etc

as well as clearing on the "reset" script

Any advice or tips on how to do that ?

Thank you so much again

Adrian