wordwrap as a button on the toolbar?

Can anybody tell me how I can customize my toolbar to include a button
to wrap cell contents?

Kind Regards
Tom

Please post all replies to this newsgroup.
Kind Regards
Tom

Please post to this newsgroup

There doesn't seem to be a built in toolbar button
available for this so you're going to have to do it the
hard way by implementing it as a macro.

Start a new blank spreadsheet.

Go to Tools, Macro, Record New Macro. Change the Macro
name to WordWrap and select Personal Macro Workbook from
the "Store Macro in" drop down.

Go to Format, Cells, Alignment and check the Wrap text box
and OK out.

Hit the stop recording button.

Now get the VB editor up with Alt-F11 and find the
Personal Macros entry in the project list - usually
says "VBAProject (PERSONAL.XLS)". Go down the tree and
expand the Modules folder, there should be a subfolder
called Module1. Double click it and you'll see the VB
code you just created.

Edit the code so it simply reads

Sub WordWrap()
Selection.WrapText = True
End Sub

Then you can close the VB editor window.

Back to the Excel screen, right click on the toolbar and
choose Customize. On the Commands tab scroll down the
list till you get to Macros. Drag one of the buttons onto
your toolbar and right click the new button. Choose the
Assign Macro option from the popup menu and you should see
your macro PERSONAL.XLS!WordWrap in the list. Select it
and click OK then close your Customize dialog.

Exit your blank sheet and say no if it asks you to save.

Load up a sheet try it !

When you exit Excel it may ask if you want to save changes
to your personal macro file. Say yes. This will get
loaded evertime you launch Excel. If you want to keep it
backed up you'll normally find it in your C:\Program
Files\Microsoft Office\Office\XLStart folder.

Now you can do this, you can do loads of cool
customisation in Excel. Have fun.

Regards

SysMan


>-----Original Message-----
>Can anybody tell me how I can customize my toolbar to
include a button
>to wrap cell contents?
>
>Kind Regards
>Tom
>
>Please post all replies to this newsgroup.
>Kind Regards
>Tom
>
>Please post to this newsgroup
>.
>

Tom

I have never been able to find a built-in button for this. I created a small
macro to toggle wrapping on/off.

Sub Wrap_Text()
With Selection
.WrapText = Not .WrapText
End With
End Sub

Stick a button on your toolbar(from Tools>Customize>Commands>Macros) and
assigned the Wrap_Text macro to it.

Edit the smiley face to a large "W" or download John Walkenbach's
BUTTONFACES.xls from here.........

http://www.j-walk.com/ss/excel/files/developer.htm


Gord Dibben Excel MVP - XL97 SR2 & XL2002


On Fri, 11 Jul 2003 15:43:20 +1000, tom mitchell <me@privacy.net> wrote:

>Can anybody tell me how I can customize my toolbar to include a button
>to wrap cell contents?
>
>Kind Regards
>Tom
>
>Please post all replies to this newsgroup.
>Kind Regards
>Tom
>
>Please post to this newsgroup

Thanks Gordon,

I will give this a try.

Enjoy your weekend
Tom

On Fri, 11 Jul 2003 14:22:52 -0700, Gord Dibben <gdibben@shaw.ca>
wrote:

>Tom
>
>I have never been able to find a built-in button for this. I created a small
>macro to toggle wrapping on/off.
>
>Sub Wrap_Text()
> With Selection
> .WrapText = Not .WrapText
> End With
>End Sub
>
>Stick a button on your toolbar(from Tools>Customize>Commands>Macros) and
>assigned the Wrap_Text macro to it.
>
>Edit the smiley face to a large "W" or download John Walkenbach's
>BUTTONFACES.xls from here.........
>
>http://www.j-walk.com/ss/excel/files/developer.htm
>
>
>Gord Dibben Excel MVP - XL97 SR2 & XL2002
>
>
>On Fri, 11 Jul 2003 15:43:20 +1000, tom mitchell <me@privacy.net> wrote:
>
>>Can anybody tell me how I can customize my toolbar to include a button
>>to wrap cell contents?
>>
>>Kind Regards
>>Tom
>>
>>Please post all replies to this newsgroup.
>>Kind Regards
>>Tom
>>
>>Please post to this newsgroup

Kind Regards
Tom

Please post to this newsgroup

HI Sysman,

thank you very much for your very clear instructions.
Made my life so much easier and it works like a charm.
I only had to include the "end with" into the code but I (totally
unfamiliar with macros & code) thanks to Gordon's response.

Thanks again, much appreciated
Tom




On Fri, 11 Jul 2003 05:51:34 -0700, "SysMan" <SysMan_999@hotmail.com>
wrote:

>There doesn't seem to be a built in toolbar button
>available for this so you're going to have to do it the
>hard way by implementing it as a macro.
>
>Start a new blank spreadsheet.
>
>Go to Tools, Macro, Record New Macro. Change the Macro
>name to WordWrap and select Personal Macro Workbook from
>the "Store Macro in" drop down.
>
>Go to Format, Cells, Alignment and check the Wrap text box
>and OK out.
>
>Hit the stop recording button.
>
>Now get the VB editor up with Alt-F11 and find the
>Personal Macros entry in the project list - usually
>says "VBAProject (PERSONAL.XLS)". Go down the tree and
>expand the Modules folder, there should be a subfolder
>called Module1. Double click it and you'll see the VB
>code you just created.
>
>Edit the code so it simply reads
>
>Sub WordWrap()
>Selection.WrapText = True
>End Sub
>
>Then you can close the VB editor window.
>
>Back to the Excel screen, right click on the toolbar and
>choose Customize. On the Commands tab scroll down the
>list till you get to Macros. Drag one of the buttons onto
>your toolbar and right click the new button. Choose the
>Assign Macro option from the popup menu and you should see
>your macro PERSONAL.XLS!WordWrap in the list. Select it
>and click OK then close your Customize dialog.
>
>Exit your blank sheet and say no if it asks you to save.
>
>Load up a sheet try it !
>
>When you exit Excel it may ask if you want to save changes
>to your personal macro file. Say yes. This will get
>loaded evertime you launch Excel. If you want to keep it
>backed up you'll normally find it in your C:\Program
>Files\Microsoft Office\Office\XLStart folder.
>
>Now you can do this, you can do loads of cool
>customisation in Excel. Have fun.
>
>Regards
>
>SysMan
>
>
>>-----Original Message-----
>>Can anybody tell me how I can customize my toolbar to
>include a button
>>to wrap cell contents?
>>
>>Kind Regards
>>Tom
>>
>>Please post all replies to this newsgroup.
>>Kind Regards
>>Tom
>>
>>Please post to this newsgroup
>>.
>>

Kind Regards
Tom

Please post to this newsgroup

Gord,

If the selection has a mixture of wrapped cells and unwrapped cells then
Selection.Wrap.Text returns Null and nothing happens.
This slight mod seems to keep things going...

'--------------------------------------------
Sub ToggleWordWrap()
On Error GoTo WrapError
If Not IsNull(Selection.WrapText) Then
Selection.WrapText = Not Selection.WrapText
Else
Selection.WrapText = False
End If
Exit Sub

WrapError:
Beep
End Sub
'------------------------------------------------

Regards,
Jim Cone
San Francisco, CA
****************************************
"Gord Dibben" <gdibben@shaw.ca> wrote in message
news:k6augvsmv1s9hjfjidhsr53okp67i4g792@4ax.com...
> Tom
>
> I have never been able to find a built-in button for this. I created a
small
> macro to toggle wrapping on/off.
>
> Sub Wrap_Text()
> With Selection
> .WrapText = Not .WrapText
> End With
> End Sub
>
> Stick a button on your toolbar(from Tools>Customize>Commands>Macros) and
> assigned the Wrap_Text macro to it.
>
> Edit the smiley face to a large "W" or download John Walkenbach's
> BUTTONFACES.xls from here.........
>
> http://www.j-walk.com/ss/excel/files/developer.htm
>
>
> Gord Dibben Excel MVP - XL97 SR2 & XL2002
>
>
> On Fri, 11 Jul 2003 15:43:20 +1000, tom mitchell <me@privacy.net> wrote:
>
> >Can anybody tell me how I can customize my toolbar to include a button
> >to wrap cell contents?
> >
> >Kind Regards
> >Tom
> >
> >Please post all replies to this newsgroup.
> >Kind Regards
> >Tom
> >
> >Please post to this newsgroup
>

Another option is to just base it on the first cell in the selection:

Option Explicit
Sub ToggleWordWrap2()
Selection.WrapText = Not Selection(1).WrapText
End Sub

All of the cells in the selection will be the same after the macro runs.

Jim Cone wrote:
>
> Gord,
>
> If the selection has a mixture of wrapped cells and unwrapped cells then
> Selection.Wrap.Text returns Null and nothing happens.
> This slight mod seems to keep things going...
>
> '--------------------------------------------
> Sub ToggleWordWrap()
> On Error GoTo WrapError
> If Not IsNull(Selection.WrapText) Then
> Selection.WrapText = Not Selection.WrapText
> Else
> Selection.WrapText = False
> End If
> Exit Sub
>
> WrapError:
> Beep
> End Sub
> '------------------------------------------------
>
> Regards,
> Jim Cone
> San Francisco, CA
> ****************************************
> "Gord Dibben" <gdibben@shaw.ca> wrote in message
> news:k6augvsmv1s9hjfjidhsr53okp67i4g792@4ax.com...
> > Tom
> >
> > I have never been able to find a built-in button for this. I created a
> small
> > macro to toggle wrapping on/off.
> >
> > Sub Wrap_Text()
> > With Selection
> > .WrapText = Not .WrapText
> > End With
> > End Sub
> >
> > Stick a button on your toolbar(from Tools>Customize>Commands>Macros) and
> > assigned the Wrap_Text macro to it.
> >
> > Edit the smiley face to a large "W" or download John Walkenbach's
> > BUTTONFACES.xls from here.........
> >
> > http://www.j-walk.com/ss/excel/files/developer.htm
> >
> >
> > Gord Dibben Excel MVP - XL97 SR2 & XL2002
> >
> >
> > On Fri, 11 Jul 2003 15:43:20 +1000, tom mitchell <me@privacy.net> wrote:
> >
> > >Can anybody tell me how I can customize my toolbar to include a button
> > >to wrap cell contents?
> > >
> > >Kind Regards
> > >Tom
> > >
> > >Please post all replies to this newsgroup.
> > >Kind Regards
> > >Tom
> > >
> > >Please post to this newsgroup
> >

--

Dave Peterson
ec35720@msn.com

Thank you Jim and Dave for the mods.

Gord

On Sat, 12 Jul 2003 06:09:26 -0500, Dave Peterson <ec35720@msn.com> wrote:

>Another option is to just base it on the first cell in the selection:
>
>Option Explicit
>Sub ToggleWordWrap2()
> Selection.WrapText = Not Selection(1).WrapText
>End Sub
>
>All of the cells in the selection will be the same after the macro runs.
>
>Jim Cone wrote:
>>
>> Gord,
>>
>> If the selection has a mixture of wrapped cells and unwrapped cells then
>> Selection.Wrap.Text returns Null and nothing happens.
>> This slight mod seems to keep things going...
>>
>> '--------------------------------------------
>> Sub ToggleWordWrap()
>> On Error GoTo WrapError
>> If Not IsNull(Selection.WrapText) Then
>> Selection.WrapText = Not Selection.WrapText
>> Else
>> Selection.WrapText = False
>> End If
>> Exit Sub
>>
>> WrapError:
>> Beep
>> End Sub
>> '------------------------------------------------
>>
>> Regards,
>> Jim Cone
>> San Francisco, CA
>> ****************************************
>> "Gord Dibben" <gdibben@shaw.ca> wrote in message
>> news:k6augvsmv1s9hjfjidhsr53okp67i4g792@4ax.com...
>> > Tom
>> >
>> > I have never been able to find a built-in button for this. I created a
>> small
>> > macro to toggle wrapping on/off.
>> >
>> > Sub Wrap_Text()
>> > With Selection
>> > .WrapText = Not .WrapText
>> > End With
>> > End Sub
>> >
>> > Stick a button on your toolbar(from Tools>Customize>Commands>Macros) and
>> > assigned the Wrap_Text macro to it.
>> >
>> > Edit the smiley face to a large "W" or download John Walkenbach's
>> > BUTTONFACES.xls from here.........
>> >
>> > http://www.j-walk.com/ss/excel/files/developer.htm
>> >
>> >
>> > Gord Dibben Excel MVP - XL97 SR2 & XL2002
>> >
>> >
>> > On Fri, 11 Jul 2003 15:43:20 +1000, tom mitchell <me@privacy.net> wrote:
>> >
>> > >Can anybody tell me how I can customize my toolbar to include a button
>> > >to wrap cell contents?
>> > >
>> > >Kind Regards
>> > >Tom
>> > >
>> > >Please post all replies to this newsgroup.
>> > >Kind Regards
>> > >Tom
>> > >
>> > >Please post to this newsgroup
>> >