Adding your own icon to the toolbar to run a macro in excel?

  • Thread starter Thread starter John
  • Start date Start date
J

John

Hello.

I have a macro in excel which changes all the numbers in column a to
different numbers.

For example it turns the numbers 100 to 500 into 1000 to 5000.

This is an example of the macro.


Sub Macro1()
'
' Macro1 Macro
' Macro recorded 14/12/2003 by John
'
' Keyboard Shortcut: Ctrl+k
'
Columns(1).Replace What:="100", Replacement:="1000",
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Columns(1).Replace What:="200", Replacement:="2000",
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Columns(1).Replace What:="300", Replacement:="3000",
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Columns(1).Replace What:="400", Replacement:="4000",
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Columns(1).Replace What:="500", Replacement:="5000",
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
End Sub


If I want to add my own icon to the toolbar in excel so that whenever
I click the icon it runs the macro, do you know how I can do this and
add it in?

Are the excel icons in the toolbar the same as the .ico files or are
they different?

Thanks very much for any help on how to do this.

John
 
You can do:

tools|customize
(create a new toolbar on the Toolbars tab--or add to an existing toolbar).
Then on the commands tab, scroll down to macros in the category box.

drag the icon to your new toolbar
Then right click on that icon (with that dialog still open)
and you can assign it the macro you want, change the button image.

When you click on that icon, excel will find your workbook and open it (if it's
not already open) and run the macro.

===

Watch out for your macro. Do you really want xlpart (maybe xlwhole to limit it
more).

Someday, when you get lots of these macros, you may want to investigate John
Walkenbach's MenuMaker program.

This doesn't actually build a toolbar, but it adds an option to the worksheet
menubar. (I find this organization easier to use when there are lots of
macros.)

http://j-walk.com/ss/excel/tips/tip53.htm
 
Back
Top