wordwrap as a button on the toolbar?

[tom mitchell]

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

 

[SysMan]

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

 

[Gord Dibben]

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

 

[tom mitchell]

Thanks Gordon,

I will give this a try.

Enjoy your weekend
Tom

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

Kind Regards
Tom

Please post to this newsgroup

 

[tom mitchell]

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

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

Kind Regards
Tom

Please post to this newsgroup

 

[Jim Cone]

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

 

[Dave Peterson]

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.

 

[Gord Dibben]

Thank you Jim and Dave for the mods.

Gord

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.