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....

Hi Corey,

Try:

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


---
Regards,
Norman



"Corey" wrote in message
...
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....

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
'<<=============



---
Regards,
Norman

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

This format returns the month as October!

Regards,
Stefi

€žNorman Jones€� ezt Ã*rta:

Hi Corey,

Try:

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


---
Regards,
Norman



"Corey" wrote in message
...
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....

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

--
HTH

Bob Phillips

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

"Corey" wrote in message
...
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....

Hi Steffi,

This format returns the month as October!

Indeed! As does my last post!


---
Regards,
Norman

On Nov 7, 10:32 am, "Corey" wrote:
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

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!


---
Regards,
Norman



"Mike Woodhouse" wrote in message
oups.com...


On Nov 7, 10:32 am, "Corey" wrote:
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

Hi Mike,

I withdraw my comment with apologies!

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

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



---
Regards,
Norman

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
"Norman Jones" wrote in message
...
Hi Corey,

Try:

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


---
Regards,
Norman



"Corey" wrote in message
...
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....

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.

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


---
Regards,
Norman

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
..


---
Regards,
Norman

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" wrote in message
...
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


---
Regards,
Norman

Hi Corey,

See me preceding response - remove the ControlSource assignment.


---
Regards,
Norman


"Corey" wrote in message
...
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???

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

did you notice my other clipboard post problem ?

ctm
"Norman Jones" wrote in message
...
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
.


---
Regards,
Norman

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. ??




"Corey" wrote in message
...
That was it.
The E2 that was in the controlsource.
Perfect.
Thanks for the help,

did you notice my other clipboard post problem ?

ctm
"Norman Jones" wrote in message
...
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
.


---
Regards,
Norman

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 ?


"Corey" wrote in message
...
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. ??




"Corey" wrote in message
...
That was it.
The E2 that was in the controlsource.
Perfect.
Thanks for the help,

did you notice my other clipboard post problem ?

ctm
"Norman Jones" wrote in message
...
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
.


---
Regards,
Norman

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
'<<=============


---
Regards,
Norman



"Corey" wrote in message
...
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 ?


"Corey" wrote in message
...
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. ??




"Corey" wrote in message
...
That was it.
The E2 that was in the controlsource.
Perfect.
Thanks for the help,

did you notice my other clipboard post problem ?

ctm
"Norman Jones" wrote in message
...
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
.


---
Regards,
Norman

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

Mike Woodhouse wrote:

On Nov 7, 10:32 am, "Corey" wrote:

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


--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info