Repetive Formatting/Creating a Toolbar button

[rllngriver]

Hi:

I work on different worksheets all of the time and have to choose format (on
different cells specific font withing the cell). I normally right click on
the font within the cell I need to format and choose format, select Font,
change the color to red, and choose strike through. How do I create a button
so that whenever I want to format any cell that I want to, I can have those
steps done immediately by clicking on a button.

I also sometimes need to format font so that is just green in color.

All this I want this button to be on the toolbar no matter which workbook I
am working on. I want it to be there all of the time.


Thank you.

 

[Texas Aggie]

Howdy, I'm going to walk you through making a toolbar button that will be
able to help you.

On the Tools menu, click Customize, and then click the Toolbars tab.
Click New.
In the Toolbar name box, type the name you want, and then click OK.
Click the Commands tab.
Do the following:

Scroll down till you see Macro and select,
then click and drag "Custom Menu Item" into the tool bar you created.

To rename the button, right click the button and goto name

Now you need to create a Macro, press Atl+F11 to open the VBA Window

Create a Module and place this sample code in there

Sub FormatCell()

With Selection.Font
.Name = "Arial Black"
.FontStyle = "Italic"
.Size = 12
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
End Sub

change as you perfer.

Now attack this macro by right clicking your toolbar you created and select
customize, then right click again and go to Assign Macro.

Then select the Macro titled "FormatCell"

Vuala your done

new every cell you want to format all you have to do is press that button

--
If this reply was helpful, please indicate that your question has been
answered to help others find anwsers to similar questions.

www.silverbirddesigns.com

Fighting Texas Aggie Class of 2009

 

[Bernard Liengme]

The OP wanted the macro to be available always. This requires putting the
subroutine in the file PERSONAL.XLS
Only then can we say "Voila, tu fini"
best wishes

 

[rllngriver]

Thank you for your response, but I am not very good at scripting. I followed
what you said and was able to get the strikethrough, but the fint did not
turn red. Is there an easier way to do this? Also, will this toolbar button
turn up in excel no matter what excel worrkbook I am working on?

 

[Texas Aggie]

You can record your own Macro,

goto View > Toolbars > Visual Basic

Click the Record Macro button (red dot) and the in the cell your working
with formate as wanted and then press "stop recording" then set that macro to
your button

Make sure that the cell you want to work with is selected before you start
recording
--
If this reply was helpful, please indicate that your question has been
answered to help others find anwsers to similar questions.

www.silverbirddesigns.com

Fighting Texas Aggie Class of 2009

 

[rllngriver]

I tried this before and it did not work. I will be working with different
workbooks and there will be different cells. Maybe I did something wrong.

 

[Texas Aggie]

It will work as long as you have that WS saved somewhere on the computer

--
If this reply was helpful, please indicate that your question has been
answered to help others find anwsers to similar questions.

www.silverbirddesigns.com

Fighting Texas Aggie Class of 2009

 

[rllngriver]

Isn't there a way to have the same custom buttons work on different
worksheets and for the buttons to automatically be on any excel sheet or
workbook that you work on?

 

[Texas Aggie]

the way I have seen some people accomplish that is to save the macro to a web
address but honestly we're getting to limits of my excel knowledge. But what
I can't anwser someone else may. I would start with either Chip Pearson, his
site is;

www.cpearson.com

or J.E. McGimpsey, his site is at;

http://www.mcgimpsey.com/excel/index.html

sorry I havent been able to help further, I been working with Excel for over
a year and I havent figured a fraction of it out yet. Thats saying much since
there are guys out there that have been working with Excel for 10+ years and
havent figured everything out yet. Good luck. If you have any questions about
the code I'd be more than happy to help with that.
--
If this reply was helpful, please indicate that your question has been
answered to help others find anwsers to similar questions.

www.silverbirddesigns.com

Fighting Texas Aggie Class of 2009

 

[rllngriver]

what is the exact script for the macro that will make the font red with
strikethrough

and what is the scrip for the macro that will make font Arial,Bold, and have
Red font?

 

[Bernard Liengme]

Maybe you have the subroutine in the wrong place. look at: David McRitchie's
site on "getting started" with VBA
http://www.mvps.org/dmcritchie/excel/getstarted.htm
To have the macro available in all workbook you must place the sub in
PERSONAL.XLS and save this in your XLSTART folder.
best wishes

 

[rllngriver]

I figured out how to do it without having to deal with script. I had to open
the personal.xls file and record each steps of whatever command button I
wanted to use by using the record macro feature. I associated those macros
with the button images of my choice and added them to the toolbar. Then I
saved the personal.xls sheet Now, when any excel sheet opens, I have the
command buttons available on the tool bar so I can format the text I select
without having to go through so many steps.