Software

How to wrap Excel text using a macro


Formats determine how Excel displays data and they're easy enough to apply. Simply select the appropriate cells, open the Format menu, and proceed accordingly.

Word has a neat feature that puts format choices on screen--Reveal Formats. Unfortunately, there's no such feature in Excel. If you apply (or remove) formats frequently, you might want to use a macro to toggle your more frequent formatting needs. For instance, the following macro toggles the Text Wrap format for a single cell, a range of cells, or even a non-contiguous range:

Sub WrapText()
  Dim rng As Range
  For Each rng In Selection
    rng.WrapText = Not rng.WrapText
  Next
End Sub

Apply a keyboard shortcut, such as Ctrl+w and you're ready to go. To create a keyboard shortcut for a macro, do the following:

  • Choose Macro from the Tools menu, and then select Macros.
  • In the Macro dialog box, click Options.
  • In the Macro options dialog box, enter a letter to combine with Ctrl.
  • Click OK.

Run the macro to apply or remove the Text Wrap format. Because it toggles the format property, you don't need two macros--one to apply and another to remove the format--this one does both.

You can use this macro to toggle most format properties. When entering the code, use Intellisense to view all the possible format properties for the defined variable.

september2007blog8r.jpg

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

1 comments
sandra.scrivens
sandra.scrivens

I love keyboard shortcuts but your suggestion of using Ctrl+w wouldn't help as it has already been assigned to Close - one I use all the time. Ctrl+Shift+W would work though.

Editor's Picks