Make Alignment options under format cells available as shortcut



Make alignment options, under Format Cells, available as a shortcut, button,
icon on the toolbar. The options that I am particularly interested in are:
Text Control, which allows you to uncheck merged cells(Merging cells is an
Icon on the format toolbar, but removing the merge is not) The wrap text
option is also something I use a lot and would like for it to be easier to
get to. Also, I am interested in vertical text alignment, which allows you
to put the text at the top of the cell.

Any help would be appreciated as I use the functions many times during the



Earl Kiosterud


You can roll your own shortcuts to do stuff like that with macros. Just
record a macro as you do the formatting. For your example of turning off
Merge Cells, do this:

Select a merged cell first.

Start the macro recorder with Tools - Macro - Record new macro. To make the
macro available when any workbook is open, set "Store macro in" to "Personal
macro workbook." You can assign the shortcut key there, or later.

Now it's recording. Don't do any selecting or other stuff -- just do the
formatting or whatever you want to have the macro do.

Stop the macro recorder (Stop button on macro recording toolbar, or Tools -
Macro - Stop recording).

Now Alt-F11 to the VBE, open the module in Personal.xls, and take out all
the stuff you don't want done. That's because the macro records the
settings for all the options in the Format - Cells - Alignment dialog, not
just the one that was changed.

Recording while turning off merge cells produced:

Sub MergeOff()
' MergeOff Macro
' Macro recorded 7/14/2005 by Earl
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
End Sub

....which you would reduce to:

Sub MergeOff()
' MergeOff Macro
' Macro recorded 7/14/2005 by Earl
With Selection
. .MergeCells = False
End With
End Sub

Or more succinctly:

Sub MergeOff()
' MergeOff Macro
' Macro recorded 7/14/2005 by Earl
Selection.MergeCells = False
End Sub

If you didn't assign the macro to a shortcut key earlier, do it now with
Tools - Macro - Macros. Using the Shift key ensures that a standard Excel
shortcut doesn't get replaced.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question