macro enabled for all workbooks

[tjb]

I need to have a macro available to run every time a workbook is opened on my
machine. It doesn't need to run automatically whenever I open a new workbook
but I want to assign a button in the Excel toolbar to have the macro execute
when I click it.

Do I use a template or something? I don't know how to do this. Thanks!

 

[JulieD]

Hi

put the macro in personal.xls (right mouse click on a sheet tab, choose view
code and on top left in the project explorer hopefully you'll see
personal.xls in bold & brackets - insert a module and copy & paste your
code there)

then create a button by choose view / toolbars / customize in the commands
tab choose Macros and drag the custom button to the toolbar, right mouse
click on it, choose assign macro, choose your macro, close the customize
box.

hope this helps
Cheers
JulieD

 

[tjb]

so do I make an excel file called "personal.xls"? And then follow thos
steps? If I just add the button and assign the macro to it, it opens the
original file when I run it.

 

[JulieD]

Hi tjb

when you right mouse clicked on the sheet tab and chose view code, did you
see a list of sheets & folders on the top left of the VBE window (try insert
/ project explorer to display / hide these). In this list you should have
seen Personal.xls .. if not try this,
press ALT & F11 to switch back to your workbook
choose tools / macros / record new macro
ensure that store macro in personal macro workbook is selected
click on OK
click on any three cells at all
stop recording the macro
now right mouse click on the sheet tab .. choose view code and you should
see Personal.xls in bold in the project explorer area in the top left.
click on it, choose insert / module
copy & paste the code in the white area that comes up on the right hand side
of the screen when you do this.

then assign that macro to the toolbar icon

if it still doesn't run properly, copy & paste your code in a reply post so
that we can see what it's actually doing.

Cheers
JulieD