Sleeper: Form Date

[Dowsey1977]

Hi,

I have a form that is used to enter Client data. Some of the data input is a date, and then this information is linked to cells on a worksheet. The problem I am having is transferring the date in the correct format. If I was to type in 04/08/05, it shows up right on the form, but then will show 8th April on the worksheet.

I have used the AfterUpdate procedure on the text box with Me.Date.Value = Format(Date.Value, "dd-mmm-yyyy"), but this still displays 8th April on the worksheet.

Does anyone know how to get 4th August 2005 on the form and sheet? I have tried changing the format of the cells on the worksheet but this just changes the formatting of 8th April!!

[Killian]

You might need to post the relevant code, but when you assign the value to the worksheet range, setting the cells number format should give you the result you want

'where myRange is the destination cell
myRange.NumberFormat = "dd mmmm yyyy"

[MWE]

Hi,

I have a form that is used to enter Client data. Some of the data input is a date, and then this information is linked to cells on a worksheet. The problem I am having is transferring the date in the correct format. If I was to type in 04/08/05, it shows up right on the form, but then will show 8th April on the worksheet.

I have used the AfterUpdate procedure on the text box with Me.Date.Value = Format(Date.Value, "dd-mmm-yyyy"), but this still displays 8th April on the worksheet.

Does anyone know how to get 4th August 2005 on the form and sheet? I have tried changing the format of the cells on the worksheet but this just changes the formatting of 8th April!!

It is my experience that date formats in Excel default to whatever you have at the system level. I have seen cases where even explicitly setting the date format after data has been written to a cell does not fix the problem (we should be thankful that Bill Gates continues to provide these little challenges; else what a boring world ...). Given your location, I would assume that you have set your system date format to be something unambiguous like dd-mmm-yyyy.

Inconsistent date formats is a pretty common problems across machines, particularly between US and non-US machines. Formats like dd/mm/yy or mm/dd/yy are particularly dangerous. It would make everything so much easier if we would all adopt a universal, unambiguous date format. I vote for dd-mmm-yyyy or yyyy-mmm-dd

[Dowsey1977]

It is my experience that date formats in Excel default to whatever you have at the system level. I have seen cases where even explicitly setting the date format after data has been written to a cell does not fix the problem (we should be thankful that Bill Gates continues to provide these little challenges; else what a boring world ...). Given your location, I would assume that you have set your system date format to be something unambiguous like dd-mmm-yyyy.

Inconsistent date formats is a pretty common problems across machines, particularly between US and non-US machines. Formats like dd/mm/yy or mm/dd/yy are particularly dangerous. It would make everything so much easier if we would all adopt a universal, unambiguous date format. I vote for dd-mmm-yyyy or yyyy-mmm-dd

I have checked the system date and all seems ok! If I add the date to the spreadsheet it looks ok, if I add the date to the form it looks ok on the form...However, it is just when the form adds it to the cell on the spreadsheet, so combining the the 2 points. I am trying to get it to be dd-mmm-yyyy, but it just ain't working!!!

[MWE]

I have checked the system date and all seems ok! If I add the date to the spreadsheet it looks ok, if I add the date to the form it looks ok on the form...However, it is just when the form adds it to the cell on the spreadsheet, so combining the the 2 points. I am trying to get it to be dd-mmm-yyyy, but it just ain't working!!!

Did you try Killian's suggestion, i.e., explicitly setting the format after the data is posted to the target cell?

Is it really important that the cell value be a date? Could it be a text value that looks like a date? If so, you could post the value to the cell using the Format procedure, for example:

Cells(10, 5) = Format(DateValue, "dd-mmm-yyyy")

[TonyJollans]

This is one of Microsoft's weaker bits of design.

Userform Textboxes contain text, not dates. If you link the textbox to a cell which is formatted as a date, input into the textbox is treated as a date if it can be interpreted as such. BUT there are no options - any ambiguity is resolved according to American date format rules (mm/dd/yy) regardless of your international settings or anything else. It's actually slightly worse than that because even when there's no ambiguity such as with your example of 04-Aug-2005, it seems to convert it to numeric format and introduce an ambiguity which it then resolves according to US rules.

I haven't played with it recently, but I have never found a satisfactory solution to this without unlinking the textbox from the cell and manually setting each independently.

Do let us know if you crack it but be aware that the AfterUpdate event is too late - Microsoft has already done it's dirty work by then - it might be possible to fool the system in the BeforeUpdate event, I don't know.

[johnske]

I have no problems with this:

Option Explicit
 
Private Sub TextBox1_AfterUpdate()
TextBox1 = Format(TextBox1, "dd mmm yyyy")
Range("A65536").End(xlUp).Offset(1, 0) = TextBox1
End Sub

After update, the value that's in textbox1 is formatted to the format specified in the first line (04/08/05 --> 04 Aug 2005) and shown in that format.

The next line tranfers that value to the s/s exactly as it's showing in the textbox (e.g. 04 Aug 2005).

However, if you use either of these forms of coding

Range("A65536").End(xlUp).Offset(1, 0).value = TextBox1.value[/vba]or[vba]Range("A65536").End(xlUp).Offset(1, 0).value = TextBox1.text

then the display in the cell will default to 08 04 05 as described by Tony.

HTH,
John


EDIT: I think you'll find this is because when it's not specified, Excel - by default - always assumes that you're referring to the Value.

So using "= TextBox1" means 'the value displayed in Textbox1'. But when we use "= TextBox1.Value" we're actually asking for 'the value of the value displayed in Textbox1', as this is showing as a date, Excel then uses its' default method for formatting dates to show you the value of this value.

[TonyJollans]

I just did a quick experiment.

Excel 2003 (original - NOT SP1) amd Windows XP Pro SP1.

International Settings are standard UK - in particular short date dd/mm/yyyy (and long date dd mmmm yyyy).

Cell A1 formatted as dd-mmm-yyyy, showing 01-Jan-2005 (cell showing as 01/01/2005 in the formula bar)

A quick check in the VBE shows:

Text: "01-Jan-2005"
Value: "01/01/2005"
Value2: 38353
Formula: "38353"

New Userform containing two textboxes.

Textbox2 completely default and exists only to have somewhee to tab out of textbox1 into.

Textbox1 ControlSource A1 - everything else left to default.

Msgbox coded in events Change, BeforeUpdate, AfterUpdate, Exit - same display (except for event identifier) showing Text, Value and BoundValue of Textbox1 and Text, Value and Value2 of Cell A1

Results:

Before showing Userform, Textbox1 shows "1/1/2005" (initialised from cell)

Show Userform ...

Overtype "1/1/" with 4

Change Event shows Textbox1 Text and Value both 42005, BoundValue 01/01/2005 (and cell unchanged)

Type space

Change event now shows Textbox1 Text and Value both "4 2005" - otherwise as before

Type "A"

Change event now shows Textbox1 Text and Value both "4 A2005" - otherwise as before

Type "u"

Change event now shows Textbox1 Text and Value both "4 Au2005" - otherwise as before

Type "g"

Change event now shows Textbox1 Text and Value both "4 Aug2005" - otherwise as before

Type space

Change event now shows Textbox1 Text and Value both "4 Aug 2005" - otherwise as before

So far so good!

Press Tab key

Change event now shows:

Textbox1.Text: 8/4/2005
Textbox1.Value: 8/4/2005
Textbox1.BoundValue: 04/08/2005

Cell A1. Text: 04-Aug-2005
Cell A1. Value: 04/08/2005
Cell A1. Text: 38568

Events BeforeUpdate, AfterUpdate and Exit (in that order) now show exactly the same as the Change event.

As far as I understand, the Change event is triggered because textbox1 was changed - but it wasn't changed by me (all I did was press tab to get out of it). It was changed by some behind-the-scenes process that I don't know how to intercept or control.

I'd love to know an answer to this one but I'm not convinced it is possible to have linked cells and non-US format dates correctly working together.