Storing Macros and VBA code in a template

[M0E-lnx]

Hi, I have an excel template that I use every day.. pretty much the same routine.. Update the date field, make the necessary changes, attach it to an email.

I thought it'd be nice to create a bit of VBA code to do the routine stuff, so I did.
I have added a button on my toolbar which has assigned the one macro that takes care of updating the date cell
I have another button that I click when I'm done with the document. This buttons is assigned another macro which grabs some information from the current worksheet, and fills in some information on the email... just ready for me to select a recipient.

All the macros and VBA code is stored in the template (.xlt) that I create each worksheet with. But here is the problem

When I click these buttons, Excel opens the last document I created and runs the macros off there rather than the template that I'm running.

Can anyone suggest a way to pull this off?

Thanks in advance

 

[Access_guy49]

I Have a temp. That I use a toolset on. I have stored my code in the PERSONAL.XLS
which is the hidden xls used to set default excel settings. Doing this works for me, perhaps it could work for you as well.
Be sure to unhide the Personal.xls,

WINDOW > UNHIDE> select personal.xls

Then go into your vbeditor and move the code over to the personal. then save the personal, make sure your buttons reference the proper function and then hide the personal

 

[Access_guy49]

P.S you may have to recreate the buttons in the personal.xls
i can't remember
Sorry.

 

[M0E-lnx]

So you keep this Personal.XLS file open @ all times?

 

[Access_guy49]

It is open every time you open excel, it's just hiden, It's a file that excel uses to save the layout of buttons.

you know how you can turn off a toolbar and it will be off in every project you have. That's because of the Personal.xls

If you create custom tools and buttons in the personal, every single project will have the ability to access those buttons. Just hide it again when you are done editing it. It is always there, just hidden

 

[M0E-lnx]

And where is this Personal.xls file located? or can I just store it anywhere?

 

[Access_guy49]

I think it's stored way down in the programfiles or something. Wherever it is located is where it needs to be located for Excel to actually use it. If you made a new one and saved it elsewhere it wouldn't make any difference. To access the Personal Open excel, click on windows (on the top toolbar) go to unhide, and then select personal.xls You will then see the personal.xls is open, go into it and move your code to it in the vbeditor. should be just a cut and paste.

 

[M0E-lnx]

I just tried that, and the unhide list doesn't show a personal.xls

Using Excel 2003 here

 

[Access_guy49]

your in a new blank excel project?

 

[M0E-lnx]

Yes.. new project

the un-hide option is grayed out
When I open my template, I can hide it and do it that way, but I need to always open that template. It'd be nice to have it setup like you said, but I can't find a personal.xls in this box... I already ran a search for it.

 

[Kiwiman]

Howzit

If you haven't used the Personal.xls before, you want to record a new macro and in the "Store macro In" select Personal Macro Workbook.

Once you hav recorded the macro, the Personal.xls workbook will become availble to be unhidden.