Excel template will not look at original code for macros

[Dan15]

Here is the situation - I have created an excel file with a large group of buttons to enter line items in a proposal. After I finish entering the data I need to save it to a different location under a different name. When I open the original template, it is now looking to the most recently created file for the macros, not in the original template. How do I create a template that is self-contained - as in it will only look at the code within the original file, not any of the other files created from it. Thank you for your help.

 

[RobBroekhuis]

Never save the original template. That way it can't be modified. Make it read only, if necessary.
Rob

 

[Dan15]

I am creating a new file from the template. The problem is when I attempt to create a second file from the original template. Now the buttons are looking at the code in the new file, not the original template. The original template has not been modified.

 

[dbsquared]

you will have to reopen your template file again. The reason being once you do a save as command the original file is renamed and moved. Meaning that your template file is no longer open. Another suggestion is to open the template file then open a new excel file copy the tempalte into it and then save the new file as you wnat and where you want.

 

[RobBroekhuis]

Or, if you no longer need the button functionality in the new document, just copy the data without buttons or code into a new workbook.
Rob

 

[Dan15]

I need to clarify - the buttons are actually part of a custom menu. Therefore the custom menu is always there. But it does not always access the same code to run the buttons. I need it to always access the code in the original template.

Thank you

 

[dbsquared]

You are running into the problem I described above. Once you do a save as your template is no loger open or being pointed at.

I think that you will have to open your template then open a new workbook and copy your data into the new workbook. then save and close the new workbook where you want it. This leaves your original template intact and open. The buttons should still be pointing to yor template.

 

[RobBroekhuis]

How is the custom menu created? Why do you need to copy the code into the new workbook? Have you considered using an AddIn for this job? That's how I've usually structured applications of that sort - allows me full control over generating fresh workbooks, while retaining all the code in a conveniently hidden, permanent location.

Rob

 

[Dan15]

This is how the spreadsheet is set up: The first page is mostly blank to start, with the exception of the title block. The custom menu has drop down menus for all of the items on page 2. When you chose an item, a vlookup function is run on page 2 to find the item on the material list. Then the description and price are set in the cells on page 1.

Once I have a list of items, I want to save this workbook under a specific job name. Then I want to be able to start over again from the blank template, creating another workbook for a different job. The problem is that when I go back to the blank template, the menu buttons are now looking to the code in the first job file I have just created, even though the code is in both files. Now two files now must be open. I don't need to save the code each time, but it does automatically.

 

[Dan15]

dbsquared, that solution may work, but it defys the goal of having the template - to be able to create new files from the originial.

Thanks-

 

[RobBroekhuis]

Your application sounds like a nice fit for an add-in. If you're considering doing that and need some help, please check back here. But even without an AddIn, you could make your code generate a new workbook without the VBA code, which would remove the complications you're seeing.
Rob

 

[Dan15]

Thank you for your help. Unfortunately, I have no experience with add-ins. If you think this is the way to go, I will need to do some research.

-Dan

 

[RobBroekhuis]

AddIns are really easy - they are basically just normal workbooks with the AddIn property set to true, and saved to the proper folder so that the user can install/uninstall it as necessary. When installed, the AddIn is always open in the background.
Rob

 

[kinrowan]

Another possible solution for this problem that I have experience with (having none with Add-ins) is to have VBA code build a temporary CommandBar. You can set the properties of the buttons (like the .OnAction event) to refer to the original file only.

However, the add-in may be a cleaner fix....

kinrowan