How do I create a customized toolbar button? Excell 2007

[Guest]

I would like to create button that would automatically change the format of a
cell's text or selected text to red with the strike through feature when I
clicked on it. How do I do that?

 

[Dave Peterson]

First, if you meant that you wanted to run a macro agains just a portion of text
in the cell, then that's not possible. But you could change the font color and
strikethrough for the whole cell.

I recorded a macro when I did one cell:

Option Explicit
Sub Macro1()
With Selection.Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = True
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 3
End With
End Sub


Next I would edit it to keep just what I want:

Option Explicit
Sub Macro1()
With Selection.Font
.Strikethrough = True
.ColorIndex = 3
End With
End Sub

Then I'd give it a nice name:

Option Explicit
Sub RedAndStrikeThrough()
With Selection.Font
.Strikethrough = True
.ColorIndex = 3
End With
End Sub

Then back to excel.

Tools|macro|Macros...
Select that macro
click on Options
Give it a nice shortcut key combination

If you don't want the shortcut key, you can hit tools|macro|macros and select
your macro (hitting alt-f8 is a quick way to see that macro dialog).

And then test it out by selecting a range and hitting that shortcut key.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[Dave Peterson]

ps. When you get lots of these macros and you don't want to use shortcut keys,
you can save them all into a dedicated workbook--and save it as an addin (so
that it's hidden from the user (you!) in excel).

But you have to give the user a way to run the macros.

For additions to the worksheet menu bar, I really like the way John Walkenbach
does it in his menumaker workbook:
http://j-walk.com/ss/excel/tips/tip53.htm

Here's how I do it when I want a toolbar:
http://www.contextures.com/xlToolbar02.html
(from Debra Dalgleish's site)

 

[Jon Peltier]

Excel 2007 has lost the ability for the user to create commandbars. Third
party approaches have started popping up. Here's one:

http://pschmid.net/office2007/ribboncustomizer/

- Jon

 

[Ron de Bruin]

If you want to use the old commandbars code see my site for a few examples.

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm

Excel 2007 has lost the ability for the user to create commandbars. Third
party approaches have started popping up. Here's one:

http://pschmid.net/office2007/ribboncustomizer/

- Jon

 

[Gord Dibben]

If you know the start and end points of the text within the cell you can use VBA
to give different fonts, sizes and colors within a cell.

Sub CellFont()
With ActiveCell.Characters(Start:=1, Length:=5).Font
.ColorIndex = 3
.Bold = True
.Underline = True
.Size = 14
End With
With ActiveCell.Characters(Start:=6, Length:=3).Font
.Superscript = True
.ColorIndex = 5
End With
With ActiveCell.Characters(Start:=10, Length:=4).Font
.Bold = True
.Size = 18
.ColorIndex = 6
End With
End Sub

Bu, as Dave says, not by selecting a portion of the text within the cell.


Gord Dibben MS Excel MVP

 

[Guest]

1. Do one of the following:
On the View menu, point to Toolbars, and then click Customize
-or-
Right click on the Top portion of excel, where the standard toolbars is
displayed and click customize.

2. In the Custimize dialog box, click the Command Tab,
3. In the Categories list, click Format.
4. In the Commands list to the right of the Categories list, click
Stikethrough button and drag and drop in the Formatting toolbar. and then
click Close button in the Customaize doalog box.

5. In the Formatting Tool bar, click the Font Color icon and select red.
6. Select any cell and then press the Fontsize button once and then the
Stirikethrough button next. You can repeat this to other cells.

Note: You can use the Format Painter button in the standard toolbar, to
apply the command.

Challa Prabhu