userform date issue

[Woodi2]

Hi, I have a userform that can display a date in textbox 11 in a cell (not
same cell each time) and I can write back to that cell using textbox11.
If I enter i.e. 6/1/2009, the data is transferred from textbox11 to the
selected cell. The cell then changes the date to read 1/6/2009. I I
activate the userform again, textbox11 reads the date correctly.
I have tried using the following code
TextBox11.Text = Format(TextBox11.Value, "dd/mmm/yyyy") but the when I
activate the userform again, the cell reverts the date again.
Any ideas

 

[Dave Peterson]

Something to try:

Option Explicit
Private Sub CommandButton1_Click()
With ActiveSheet.Range("A1")
.NumberFormat = "mmmm dd, yyyy"
.Value = CDate(Me.TextBox1.Value)
End With
End Sub
Private Sub UserForm_Initialize()
Me.TextBox1.Value = "11/12/2008"
End Sub

VBA's help (in xl2003) says this about the cDate() function:

CDate recognizes date formats according to the locale setting of your system.
The correct order of day, month, and year may not be determined if it is
provided in a format other than one of the recognized date settings. In
addition, a long date format is not recognized if it also contains the
day-of-the-week string.

Personally, I'd consider using something different than a textbox.

Either a calendar control...

Ron de Bruin has some notes:
http://www.rondebruin.nl/calendar.htm

Or even 3 textboxes/comboboxes/spinners/scrollbars.
One for the month, one for the day and one for the year.

Then you wouldn't have to worry about the user's intent when they typed:
01/02/03

(is it Jan 2, 2003 or 2001-Feb-03 or what.)

 

[Woodi2]

Thanks Dave. I'm a newbie to this so dont fully understand everything.
Below is a the same problem that another user has had (a better explanation
here).I have a textbox on a userform which reads a date from a cell in
spreadsheet.
Problem I am getting is each time I open the form the format of the date
changes in both the the textbox and cell. ie if cell is format dd/mm/yyyy the
Textbox shows mm/dd/yyyy. When I open the form again, it is the oposite of
the first result?
I have tried using Format eg Textbox1.Text = Format(Cells(1,2).value,
"dd/mm/yyyy") but the date still shows different format for Textbox & Cell!
Can anyone explain why this should happen & how I show to the Texbox the
cell value & format without changing it please??

Tom Ogilvy replied with the following which I have not understood
Don't link the checkbox to the cell. Load the checkbox with code.
always convert a string date with Cdate - it will pay attention to your
local settings.
How do I use CDate with my textbox, in this case textbox11.
Thanks

 

[Dave Peterson]

First, are you linking the cell to the textbox on the userform?

Look in the properties window for that textbox (for controlsource) and look at
the code for something like:

me.textbox1.controlsource = ....

If you're using that ControlSource, then this means the textbox is "linked"
directly to the cell. You'll have to drop entry in the properties window or
modify the code (or both!).

By using code to populate the listbox (what you're trying to do) and the cell,
you stop this bad behavior that you're seeing.

 

[Woodi2]

Dave, I haven't linked the textbox to any cell. The userform I have created
opens when I click any cell in column 'C'. I then have a Public Sub
CommandButton3_Click() that activates when the userform is opened, this then
imports all data from my worksheet into the userform using
Me.TextBox11.Value = ActiveCell.Offset(0, 9).Value (this is one of 9
Textbox's). If the offset cell has any data in that cell it is displayed on
the userform. I can then send the data from the userform using another
CommandButton as follows ActiveCell.Offset(0, 9) = TextBox11.Value at this
point excel changes the date format. I read the link you poseted and now
understand why it does this but dont know how to fix it.

The controlSource in properties is empty for this textbox.