best storage location for macros

[chookers]

Howdy,

I've only ever had to use macros from my own computer, not have to send a doc out where the macros will have to work on someone else's. I had them saved in 'this workbook' (it's a template) but it looks like when the doc arrives to the other computer it's looking for the macros in the last document the template was used for (ie I think it's looking for the last "document 1" based on the template, which is of course on MY computer, not the recipients).

I have noticed this before actually but where is the proper place to save macros that need to "travel"??

Thanks for helping me understand this, my book only confused the issue!

Cheers!

 

[PaddyD]

Not a macro expert, but my thoughts...

If you record a macro & store it in 'This workbook', the VBA editor shows it as being stored in the modules. You could cut the macro from there to store it as an excel object in 'This workbook'. Note: A macro recorded using the 'Record Macro' command will turn up as 'Macro1' in the macro list - if you do the cut paste, it turns up as 'ThisWorkbook.Macro1'.

I'm sure there are alternatives, but it might work.
Paddy

 

[NateO]

Chookers, you'll want the macros stored where they ought to be. Worksheet events in worksheet modules, workbook events in the 'thisworkbook' module. Unless you call a procedure with a workbook name proceeding the procedure, and your sent workbook contains all of the applicable modules, it should work. Perhaps post the problematic 'thisworkbook' code.

_________________
Cheers,<font size=+2><font color="red"> Nate<font color="blue">O</font></font></font>

This message was edited by NateO on 2002-05-06 16:59

 

[chookers]

Thanks for that Nate et al, Nate, there are actually 7 different macros that go with 7 different buttons, am having the same trouble with all of them. If I look in VBA Project Explorer, you've got the 'Microsoft Excel Objects Folder', and the 'Modules' folder. Some of these codes open the toolbar, then close it when the workbook is opened, some cut and paste from one sheet to another, etc. At the moment all the codes are in the modules folder. Are you saying maybe to paste them all to 'This workbook' under 'Microsoft Excel Objects' instead? Does that work??? I thought they were meant to stay under modules?

Sorry- I'm a bit fuzzy on this for some reason (well, lack of experience basically). Thanks for helping to educate me!

Cheers, Lauren

 

[NateO]

Laur,

No, don't store all your macros in 'thisworkbook.'

Anytime you store a 'macro' it is within a module, there are different types though....No module is better than another, they just serve different purposes (not to be profound).

So no, what I'm saying is keep these procedures in normal modules, unless they are workbook events (in which case, they would go in the 'thisworkbook' module), it doesn't sound like they are. You would know if the procedure (macro) is a workbook event, i.e., closing the workbook.....

It's hard for me to say why your macro won't transfer, don't have much info....

When you send someone a file (look in your sent folder), is the intended code in place? What is your procedure for delivering files to folks (yourself?)? Try and run the empty code and show the error line (hit debug when it goes bad).

Cheers, Nate
This message was edited by NateO on 2002-05-06 20:20

 

[chookers]

Nate, yes I've been emailing this out. I'm going to email this to myself and have a look at what's going on tonight. Will post again tomorrow to see if you or anyone can help -

As near as I can tell though, yes, I'm sending a template and when it's opened it seems to be giving an error message about can't find the macro in (for example) English1.xlt, which is basically document1 opened from the 'English' template, which I tested the macro in. I then closed it, sent the template off, but it's just sort of looking for it in the LAST document it was used in, not the current one.

Very bizarre. Will try to find out more tonight when I email myself.

Thanks very kindly, L