Why can I not get a date in a form text date to format as I want?

[kiwicolin]

Hi

I have a textbox on my userform that shows the current date when opened. No matter what I try I can not get the format to be in the UK style i.e. dd/mm/yy it always shows the US style mm/dd/yy

It used to work in excel 2003 with txtdate.Value = Format(txtdate, "dd/mm/yy") but fails in 2007 with can't find project or library.
I have also tried Me.txtdate.Value = Format(Me.txtdate.Value, "dd/mm/yy")) with the same result.

Any ideas?

Thank you

 

[Hermanito]

How do you fill the textbox with the date?
Do you manually enter it when the userform is shown? Or set it in the properties of the textbox at designtime? Or set it to some value at runtime?

My guess is that the stringvalue of the textbox is no longer recognised as a date, and thus cannot be formatted as such.

Try this:

txtDate.Value = Format(DateValue(txtDate.Value), "dd/mm/yy")

 

[kiwicolin]

Hi

Thank you for your reply.

It is taken from a cell on the sheet and is in serial format on the sheet.

The code you have suggested has the same error message. It is 'Format' that is highlighted in the debugger when this occurs.

Regards

 

[Hermanito]

strange, Format is not in any special library as far as I know... it is a standard function.

Can you try this in the Immediate Window:

?format(datevalue("12/06/2011"),"dddd dd mmmm yyyy")

Does that also give an error? Or does it reformat the date as expected?

 

[Norie]

Colin

Sounds like you might have a problem with you references.

Check under Tools>References for anything marked as 'MISSING'.

Note what it is then uncheck it, save your workbook and reopen.

Now try the code.

If that doesn't work post back, and tell us the name, and version if possible, of the reference you unchecked.

By the way, if you want to get the value from the cell as it is on a worksheet then you can use the Text property of the cell instead of Value.

Mind you, if you do have it in serial format on the sheet you will need Format.

 

[kiwicolin]

Norie

That has done the trick and fixed one or two other niggles I was having. Phew thought I was losing the plot and probably was!

Another learning for me.

Many thanks.