Hello Fellow Excellers.
When I enter a date in a cell and give it a long format. e.g. dddd dd mmmm
yyyy, so that it would read Monday 1 January 2006, then the wrap text
facility does not work.
Is there any way to put in an 'alt-enter' type character so that I could
have the Monday on the first line and the rest underneath.
--
Your help is and always has been very much appreciated.
Thanking you in anticipation.
--- --- ---
Big Rick
Not exactly what you're after, but perhaps worth a try
(Hang around for views from others ..)
Assuming dates entered in A1 down
Put in B1:
=TEXT(A1,quot;ddddquot;)amp;CHAR(10)amp;TEXT(A1,quot;dd mmmm yyyyquot;)
Format B1 to wrap text amp; copy B1 down
Col B will return the desired display
--
Rgds
Max
xl 97
---
Singapore, GMT 8
xdemechanik
savefile.com/projects/236895
--
quot;Big Rickquot; gt; wrote in message
...
gt; Hello Fellow Excellers.
gt; When I enter a date in a cell and give it a long format. e.g. dddd dd mmmm
gt; yyyy, so that it would read Monday 1 January 2006, then the wrap text
gt; facility does not work.
gt; Is there any way to put in an 'alt-enter' type character so that I could
gt; have the Monday on the first line and the rest underneath.
gt; --
gt; Your help is and always has been very much appreciated.
gt; Thanking you in anticipation.
gt; --- --- ---
gt; Big Rick
Many thanks.
This is a lot further than I would of got.
I would still like a solution if possible for the way described, as I would
like to use it in other items, but your formula does work on this occasion.
I am forever grateful and in your debt.
--
Big Rickquot;Maxquot; wrote:
gt; Not exactly what you're after, but perhaps worth a try
gt; (Hang around for views from others ..)
gt;
gt; Assuming dates entered in A1 down
gt;
gt; Put in B1:
gt; =TEXT(A1,quot;ddddquot;)amp;CHAR(10)amp;TEXT(A1,quot;dd mmmm yyyyquot;)
gt; Format B1 to wrap text amp; copy B1 down
gt; Col B will return the desired display
gt; --
gt; Rgds
gt; Max
gt; xl 97
gt; ---
gt; Singapore, GMT 8
gt; xdemechanik
gt; savefile.com/projects/236895
gt; --
gt; quot;Big Rickquot; gt; wrote in message
gt; ...
gt; gt; Hello Fellow Excellers.
gt; gt; When I enter a date in a cell and give it a long format. e.g. dddd dd mmmm
gt; gt; yyyy, so that it would read Monday 1 January 2006, then the wrap text
gt; gt; facility does not work.
gt; gt; Is there any way to put in an 'alt-enter' type character so that I could
gt; gt; have the Monday on the first line and the rest underneath.
gt; gt; --
gt; gt; Your help is and always has been very much appreciated.
gt; gt; Thanking you in anticipation.
gt; gt; --- --- ---
gt; gt; Big Rick
gt;
gt;
gt;
You're welcome !
As mentioned in the response,
do hang around awhile for views from others.
There just might be a way to do it directly (perhaps via vba ?)
that others may step-in to offer you.
--
Rgds
Max
xl 97
---
Singapore, GMT 8
xdemechanik
savefile.com/projects/236895
--
quot;Big Rickquot; gt; wrote in message
...
gt; Many thanks.
gt; This is a lot further than I would of got.
gt; I would still like a solution if possible for the way described, as I
would
gt; like to use it in other items, but your formula does work on this
occasion.
gt;
gt; I am forever grateful and in your debt.
gt; --
gt; Big Rick
One way
Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = quot;H1:H10quot;
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If IsDate(.Value) Then
If .NumberFormat = quot;dddd dd mmmm yyyyquot; Then
.Value = Format(.Value, quot;dddd quot; amp; vbLf amp; quot;dd mmmm yyyyquot;)
End If
End If
End With
End If
ws_exit:
Application.EnableEvents = True
End Sub
'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.
--
HTH
Bob Phillips
(remove nothere from email address if mailing direct)
quot;Maxquot; gt; wrote in message
...
gt; You're welcome !
gt;
gt; As mentioned in the response,
gt; do hang around awhile for views from others.
gt;
gt; There just might be a way to do it directly (perhaps via vba ?)
gt; that others may step-in to offer you.
gt; --
gt; Rgds
gt; Max
gt; xl 97
gt; ---
gt; Singapore, GMT 8
gt; xdemechanik
gt; savefile.com/projects/236895
gt; --
gt; quot;Big Rickquot; gt; wrote in message
gt; ...
gt; gt; Many thanks.
gt; gt; This is a lot further than I would of got.
gt; gt; I would still like a solution if possible for the way described, as I
gt; would
gt; gt; like to use it in other items, but your formula does work on this
gt; occasion.
gt; gt;
gt; gt; I am forever grateful and in your debt.
gt; gt; --
gt; gt; Big Rick
gt;
gt;
Dear Bob
I'm afraid that I have never got to grips with any form of VBA before. I
have followed the on screen insructions and pasted the code into the sheet1
tab, but where do I go from there.
What I have done is simply pasted the code in, closed the screen, and
entered a date in H1 but the wrap is not working. I have still formatted the
cell as wrap text but this did not help. I know that I am going wrong
somewhere, but I know that you are the one to guide me through.
Best Regards
--
--
Big Rickquot;Bob Phillipsquot; wrote:
gt; One way
gt;
gt; Private Sub Worksheet_Change(ByVal Target As Range)
gt; Const WS_RANGE As String = quot;H1:H10quot;
gt;
gt; On Error GoTo ws_exit:
gt; Application.EnableEvents = False
gt; If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
gt; With Target
gt; If IsDate(.Value) Then
gt; If .NumberFormat = quot;dddd dd mmmm yyyyquot; Then
gt; .Value = Format(.Value, quot;dddd quot; amp; vbLf amp; quot;dd mmmm yyyyquot;)
gt; End If
gt; End If
gt; End With
gt; End If
gt;
gt; ws_exit:
gt; Application.EnableEvents = True
gt; End Sub
gt;
gt; 'This is worksheet event code, which means that it needs to be
gt; 'placed in the appropriate worksheet code module, not a standard
gt; 'code module. To do this, right-click on the sheet tab, select
gt; 'the View Code option from the menu, and paste the code in.
gt;
gt;
gt;
gt;
gt; --
gt; HTH
gt;
gt; Bob Phillips
gt;
You can use format|cells|number tab
Give it a custom format of:
dddd(alt-0010)dd mmmm yyyy
Hit and hold the alt key while typing 0010 on the numeric keypad--not above the
QWERTY keys.
And turn on wrap text
format|cells|alignment tab
But be aware that you'll have to adjust the rowheight yourself (autofitting
won't work).
And if you make the cell too narrow for the whole string (not after wrapping
text), you'll see ###'s.
====
Another format to consider:
dddd* dd mmmm yyyy
This works pretty neat when you widen the column.
Big Rick wrote:
gt;
gt; Hello Fellow Excellers.
gt; When I enter a date in a cell and give it a long format. e.g. dddd dd mmmm
gt; yyyy, so that it would read Monday 1 January 2006, then the wrap text
gt; facility does not work.
gt; Is there any way to put in an 'alt-enter' type character so that I could
gt; have the Monday on the first line and the rest underneath.
gt; --
gt; Your help is and always has been very much appreciated.
gt; Thanking you in anticipation.
gt; --- --- ---
gt; Big Rick
--
Dave Peterson
Hi Rick
Copy the following, then right click on Worksheet Tab, Select View code
and Paste into the white pane.
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count gt; 1 Then Exit Sub
If Target.Row lt;2 Then Exit Sub
If Target.Column gt; 1 Then Exit Sub
If Not Intersect(Me.Range(quot;A:Aquot;), .Cells) Is Nothing Then
Application.EnableEvents = False
With Me.Cells(.Row, quot;Aquot;)
.Value = Format(.Value, quot;ddddquot; amp; vbLf amp; quot;d mmmm yyyyquot;)
End With
Application.EnableEvents = True
End If
End With
End Sub
This assumes that you want all entries in Column A to be adjusted.
If you want a different column, change quot;Aquot;'s to the column Letter
required, change Target.Column number to lt;gt; and the column number for
the entry.
Target.row is set to ignore row 1, assuming this is your header, change
as appropriate.--
Regards
Roger Govierquot;Maxquot; gt; wrote in message
...
gt; You're welcome !
gt;
gt; As mentioned in the response,
gt; do hang around awhile for views from others.
gt;
gt; There just might be a way to do it directly (perhaps via vba ?)
gt; that others may step-in to offer you.
gt; --
gt; Rgds
gt; Max
gt; xl 97
gt; ---
gt; Singapore, GMT 8
gt; xdemechanik
gt; savefile.com/projects/236895
gt; --
gt; quot;Big Rickquot; gt; wrote in message
gt; ...
gt;gt; Many thanks.
gt;gt; This is a lot further than I would of got.
gt;gt; I would still like a solution if possible for the way described, as I
gt; would
gt;gt; like to use it in other items, but your formula does work on this
gt; occasion.
gt;gt;
gt;gt; I am forever grateful and in your debt.
gt;gt; --
gt;gt; Big Rick
gt;
gt;
If you copied the code to the sheet module as described, you should have got
the two lines as required. It worked fine in my tests.
--
HTH
Bob Phillips
(remove nothere from email address if mailing direct)
quot;Big Rickquot; gt; wrote in message
...
gt; Dear Bob
gt; I'm afraid that I have never got to grips with any form of VBA before. I
gt; have followed the on screen insructions and pasted the code into the
sheet1
gt; tab, but where do I go from there.
gt; What I have done is simply pasted the code in, closed the screen, and
gt; entered a date in H1 but the wrap is not working. I have still formatted
the
gt; cell as wrap text but this did not help. I know that I am going wrong
gt; somewhere, but I know that you are the one to guide me through.
gt;
gt; Best Regards
gt; --
gt; --
gt; Big Rick
gt;
gt;
gt; quot;Bob Phillipsquot; wrote:
gt;
gt; gt; One way
gt; gt;
gt; gt; Private Sub Worksheet_Change(ByVal Target As Range)
gt; gt; Const WS_RANGE As String = quot;H1:H10quot;
gt; gt;
gt; gt; On Error GoTo ws_exit:
gt; gt; Application.EnableEvents = False
gt; gt; If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
gt; gt; With Target
gt; gt; If IsDate(.Value) Then
gt; gt; If .NumberFormat = quot;dddd dd mmmm yyyyquot; Then
gt; gt; .Value = Format(.Value, quot;dddd quot; amp; vbLf amp; quot;dd mmmm
yyyyquot;)
gt; gt; End If
gt; gt; End If
gt; gt; End With
gt; gt; End If
gt; gt;
gt; gt; ws_exit:
gt; gt; Application.EnableEvents = True
gt; gt; End Sub
gt; gt;
gt; gt; 'This is worksheet event code, which means that it needs to be
gt; gt; 'placed in the appropriate worksheet code module, not a standard
gt; gt; 'code module. To do this, right-click on the sheet tab, select
gt; gt; 'the View Code option from the menu, and paste the code in.
gt; gt;
gt; gt;
gt; gt;
gt; gt;
gt; gt; --
gt; gt; HTH
gt; gt;
gt; gt; Bob Phillips
gt; gt;
Hey, that's quite sneaky Dave.
I would never have thought about entering control characters into the
number format.
Very effective.
--
Regards
Roger Govierquot;Dave Petersonquot; gt; wrote in message
...
gt; You can use format|cells|number tab
gt;
gt; Give it a custom format of:
gt; dddd(alt-0010)dd mmmm yyyy
gt;
gt; Hit and hold the alt key while typing 0010 on the numeric keypad--not
gt; above the
gt; QWERTY keys.
gt;
gt; And turn on wrap text
gt; format|cells|alignment tab
gt;
gt; But be aware that you'll have to adjust the rowheight yourself
gt; (autofitting
gt; won't work).
gt;
gt; And if you make the cell too narrow for the whole string (not after
gt; wrapping
gt; text), you'll see ###'s.
gt;
gt; ====
gt; Another format to consider:
gt; dddd* dd mmmm yyyy
gt; This works pretty neat when you widen the column.
gt;
gt;
gt;
gt; Big Rick wrote:
gt;gt;
gt;gt; Hello Fellow Excellers.
gt;gt; When I enter a date in a cell and give it a long format. e.g. dddd dd
gt;gt; mmmm
gt;gt; yyyy, so that it would read Monday 1 January 2006, then the wrap text
gt;gt; facility does not work.
gt;gt; Is there any way to put in an 'alt-enter' type character so that I
gt;gt; could
gt;gt; have the Monday on the first line and the rest underneath.
gt;gt; --
gt;gt; Your help is and always has been very much appreciated.
gt;gt; Thanking you in anticipation.
gt;gt; --- --- ---
gt;gt; Big Rick
gt;
gt; --
gt;
gt; Dave Peterson
留言列表
