Excel VBA Userform Date issue

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

Hi Ozzie 

thank you - better to check everything from scratch - and yes all is in order as per UK dd/mm/yyyy settings as default regional settings - as mentioned (im from uk!!)

So Ill try the file you sent across but Im also wondering to try some along the lines of setting the txtbox value as a variable like

Sub_txtdate_afterupdate()

Dim=Calldate As Date

On error resume next

Calldate = CDate (Me.txtdate.value)

Me.Calldate.Value = Format(Me.txtdate.Value, "dd/mm/yyyyy"

Ignore the syntax mistakes etc but do you think that may work? and the same for datefinished - have I missed anything?

Adrian

the date in the worksheet formula bar still shows mm/dd/yyyy but thats working ok at the minute

Hi Adrian,

This suggests one of two things to me. That your Regional date setting might actually be m/d/y format or there is a bug in Excel whereby Windows is not recognizing your Windows date settings. The date displayed in the formula bar (and also when entering/editing a date in the Formula Bar) should always be in the regional date setting irrespective of the NumberFormat used in the worksheet cells to display the date. In my case it is always in d/m/y format in the Formula Bar even if I have NumberFormat set to m/d/y on the worksheet.

I'd like to ask you to check your Windows date settings. Please don't take any offence to me asking this or setting this out in step by step guidelines because I have come across situations previously where people have believed they have done something correctly but in fact they have followed the wrong procedure.

I can only provide these guidelines in English so you might need to interpret.

1. In Windows 10, Select Windows icon (Bottom left of screen)
2. Select Settings (Gear cog like icon)
3. Select Time and Language
4. Maximize the window (This is because the position of what what is displayed on the screen is dependent on the size of the window and also the left column might not be visible and it becomes difficult to explain the following)
5. Select "Date, time & regional formatting" (Top right of screen when screen is maximized)
6. Under "Regional format data" heading does it look like the following screen shot with the Short date in d/m/y format (where I have enclosed in the red line)?

I am not suggesting that you change your regional date settings if it is normally m/d/y because if your application needs to be run by another person then they would also need to change their date settings and that is not practical or advisable. However, if we are certain of your date settings then the project can be handled accordingly and using the VBA Split function and DateSerial function to convert a Text Date does that.