Odd Macro/Template behavior in Excel 1

[hessrk]

In both Excel 2000 and Excel XP I have noticed this. I created a template, and in the template there is a macro (I also created) In order to use this macro, I press a custom menu button. The template saved as Template.xlt

When I want a new worksheet from this template (Template.xlt), the worksheet is called Template1.xls ...this is normal. When I run the macro, it works, when I look at where the the custom menu button, and where the macro is assigned, it points to Template1.xls...This is fine...

But, when I look at Template.xlt and look at where the custom button macro points to, it also points to Template1.xls. I want the template macro to point to the template, not the workbook spawned from the template.

I am sure this can't be how this works...
Am I suppose to use Add-ins?
Is the normal?

Any help would be appreciated.
thx,
Rodney Hess
rkhess@hot.rr.com

 

[nosugar]

Just a quick thought. Are you looking at the template live or are you looking at the template before you run the MACRO?

David

 

[hessrk]

When you say 'live', if you mean at the template and not the worksheets...yes...otherwise I am not sure what you mean.

And this occurs before and after I run the macro.

I just created a macro, assigned it to a custom menu bar and saved the template. When I 'click' on the template, and a worksheet appears, with the custom tool bar's macro pointing back to the template. I have found similar posts to this problem on mrexcel.com, but no answer

 

[nosugar]

If you created the custom menu bar to hold the Macro button and saved it in the Template.xlt, it would be kind of like a circular reference when you try to open to the template you are using.

If you saved the MACRO globally (in your personal macro workbook), you can invoke it from any workbook and open the template in question. The toolbar button can be placed on any tool bar.

I'm not sure I answered your question, but I hope my comments stimulate some ideas.

David

 

[hessrk]

I understand the circular reference concern...and it now makes sense...

But what if this is a macro for a customer? then am I suppose to place my macro in each of their 'personal macro workbooks'? Wouldn't this be the point for an add-in?

Since this template is for a customer(s), what is the best solution?

Thx for your attention to this...

Rodney

 

[nosugar]

Keep in mind, I am giving you my opinion and I have not worked in this area in a while.

If you are concerned about the customer not using the process correctly, either option will have its problems. If this process is going to be deployed on a network, the admin guy can put the file anywhere you want.

As an Add-in, it will need to be "installed" so for it to appear on the Tools menu.

As a Macro, the file will need to be copied to each machine and saved to the 'personal macro workbooks'folder.

My guess is that these users may not have any Macros now, so you could just create the folder and copy it to the right place on their machines.

With either of these options you can create a custom tool bar button for your user to click on, thereby reducing keystrokes and the chance of error.

Good luck with it. You are close to what You think is the right way.

David