If I edit and save the template (by re-setting the macro paths) and then re-open it everything works okay, all the macros work as expected and are pointing to the current open workbook- which is what you'd expect.
If I now close Excel and then re-open the template none of the macros will work, the path to each macro has been altered so that Excel now looks on the hard drive for the macros rather than in the open workbook. The filename it looks for is the filename of the open workbook.
I'm obviously doing something wrong but I can't figure out exactly what.
TemplateName1.xls is a workbook, not a template. Templates all have an .xlt extension.
When you save a template you need to ensure that you have selected Save As and then at the bottom of the resulting dialogue box ensure that you have selected the type Template (.xlt) from the drop down list under the side heading of Save as type...
I open a template using Start> New Office Document> General Tab and select a template.
When the template loads the workbook name displayed is TemplateName1.xls, which is what you'd expect.
If I now click a button in the workbook I get the above error message, it seems Excel is looking for the macro on the hard disk rather then in the current open workbook, where the macros should be.
If I edit the template to correct the paths, save it, and re-open it, it all works okay. If I exit Excel (workbook not saved) and then open the template again I get the error until I again edit the template.