Date Value in wrong format on userform

[Corey]

I have a textbox that displays the value of a cell(E2), but the worksheet displays:
31 October 2006, yet the userform textbox value shows 10/31/06.
Is there a way i can change this to display as the sheet view (31 October 2006) ?


Corey....

 

[Norman Jones]

Hi Corey,

Try:

'=============>>
Private Sub UserForm_Initialize()
Me.TextBox1.Value = _
Format(Range("E2").Value, "dd/mm/yy")
End Sub
'<<=============


---
Regards,
Norman



I have a textbox that displays the value of a cell(E2), but the worksheet
displays:
31 October 2006, yet the userform textbox value shows 10/31/06.
Is there a way i can change this to display as the sheet view (31 October
2006) ?


Corey....

 

[Norman Jones]

Hi Corey,

Or, more consistent with your indicated format:

'=============>>
Private Sub UserForm_Initialize()
Me.TextBox1.Value = _
Format(Range("E2").Value, "dd mmmm yyyy")
End Sub
'<<=============

 

[Stefi]

Me.TextBox1.Value = _

Format(Range("E2").Value, "dd/mmmm/yy")

This format returns the month as October!

Regards,
Stefi

„Norman Jones†ezt írta:

 

[Bob Phillips]

TextBox1.Text = Format(TextBox1.Text,"d mmmm yyyy")

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

I have a textbox that displays the value of a cell(E2), but the worksheet
displays:
31 October 2006, yet the userform textbox value shows 10/31/06.
Is there a way i can change this to display as the sheet view (31 October
2006) ?


Corey....

 

[Norman Jones]

Hi Steffi,

This format returns the month as October!

Indeed! As does my last post!

 

[Mike Woodhouse]

I have a textbox that displays the value of a cell(E2), but the worksheet displays:
31 October 2006, yet the userform textbox value shows 10/31/06.
Is there a way i can change this to display as the sheet view (31 October 2006) ?

If your userform knows the address of the cell, or has a range that
points to it, then the .Text property should display the date as it is
seen in the worksheet...

Mike

 

[Norman Jones]

Hi Mike,

If your userform knows the address of the cell, or has a range that
points to it, then the .Text property should display the date as it is
seen in the worksheet...

I think that you will find that, without an intervening format instruction,
the textbox will speak with VBA's intrinsic American accent!

 

[Norman Jones]

Hi Mike,

I withdraw my comment with apologies!

Me.TextBox1.Text = Range("E2").Text

will display the worksheet's visual representation of the date,

 

[Corey]

thanks for the reply Norman

I now can get 31/10/06 instead of 10/31/06,
But my aim is for "31 October 2006"

I tried changing the .value to .text but no go.
Any idea's

ctm

 

[Corey]

Ina ctual fact i am now geting what i originally had.
I read you other post and tried:

Private Sub UserForm_Initialize()
Me.TextBox1.Value = _
Format(Range("E2").Value, "dd/mm/yy")
End Sub

And placed this in the userform code
Is that where is belongs?

I do not get any change in the textbox apperanace ????

Is there somehting i need to change in the textbox properties also ??

I have it linked to cell E2.

 

[Norman Jones]

Hi Corey,

I now can get 31/10/06 instead of 10/31/06,
But my aim is for "31 October 2006"

I tried changing the .value to .text but no go.
Any idea's

Either:

Me.TextBox1.Value = _
Format(Range("E2").Value, "dd mmmm yyyy")

or

Me.TextBox1.Text = Range("E2").Text

worked for me.

The first stipulates the format of the TextBox, the second returns the text
as seen in the worksheet

 

[Norman Jones]

Hi Corey,

Ina ctual fact i am now geting what i originally had.
I read you other post and tried:

Private Sub UserForm_Initialize()
Me.TextBox1.Value = _
Format(Range("E2").Value, "dd/mm/yy")
End Sub

And placed this in the userform code
Is that where is belongs?
Yes.

I do not get any change in the textbox apperanace ????

Is there somehting i need to change in the textbox properties also ??

I have it linked to cell E2.

Delete the ControlSource property value and retry the suggested code
..

 

[Corey]

I seem to still get, in the textbox properties under the text tab
the10/31/2006 even though i did not put it there.

I think this may be causing the absence of 31 October 2006.

I can type that in the text tab but i then get no text displayed on the
userform at all.

tried the
Me.TextBox1.Text = Range("E2").Text

but i still get the same 10/31/2006???

 

[Norman Jones]

Hi Corey,

See me preceding response - remove the ControlSource assignment.

 

[Corey]

That was it.
The E2 that was in the controlsource.
Perfect.
Thanks for the help,

did you notice my other clipboard post problem ?

ctm

 

[Corey]

Ummm,

I found that now if the date in cell E2 changes the date on the userform
does NOT.

As there is no link to the cell.
I tried leaving the :
Private Sub UserForm_Initialize()
Me.TextBox1.Text = Range("E2").Text
End Sub



but it seems to work initially then NOT. ??

That was it.
The E2 that was in the controlsource.
Perfect.
Thanks for the help,

did you notice my other clipboard post problem ?

ctm

 

[Corey]

It seems to be if i go intot he code and check the userform agian,
then exit the date updates on the userform,
then remains the same until i go into the code angain?

Is that strange?

Is there a REFRESH code to make this happen ?

Ummm,

I found that now if the date in cell E2 changes the date on the userform
does NOT.

As there is no link to the cell.
I tried leaving the :
Private Sub UserForm_Initialize()
Me.TextBox1.Text = Range("E2").Text
End Sub



but it seems to work initially then NOT. ??

 

[Norman Jones]

Hi Corey,

Replace your code with the following:

'=============>>
Private Sub UserForm_Initialize()
With Me.TextBox1
.ControlSource = "E2"
.Text = Format(.Text, "dd mmmm yyyy")
End With
End Sub

'-------------------->>

Private Sub TextBox1_Change()
With Me.TextBox1
.Text = Format(.Text, "dd mmmm yyyy")
End With
End Sub
'<<=============

 

[Andy Pope]

Hi,

A word of caution with the .Text property.
If the column is not wide enough to display the date and instead
displayed ####### that is what will be returned in the textbox.

Cheers
Andy