How to use templates with macros

[Andrew]

Hello,
I have a template which I used for years with excel 2003, and now it
doesn't work with excel 2007. This template has macros in it, and a
couple active buttons which when pressed, create a non-template file
based on the template prototype. The new non-template file also has
macros in it. So, when I try to run the file as a template, I get an
error that says that I cannot save the file into a macro-free
workbook.

I have tried saving my template as an xltm, and I have tried re-
writing the macro so that it saves the new file as an xlsm (macro-
enabled workbook). I cannot get this to work, and I am beginning to
believe that this is one of the flaws of excel 2007 (I wish so much
that microsoft would have simply billed us twice for excel 2003
instead of forcing us to use the security-obsessed 2007).
Anyway...please excuse my rant. This macro/template works perfectly
fine if I use it as an xls file. But I would prefer to have it run as
a macro so that people cannot accidentally edit the template file.

Can anyone explain to me how to make this VB code into a template.
This is based on an on screen button. Implementing this will cause
the same error I have been getting. If you can figure this out, I
would appreciate it.

Private Sub CommandButton1_Click()
Cells(1, 1) = "template works"
ThisWorkbook.SaveAs "template-enabled-workbook.xlsm"
End Sub

OR... try this

Private Sub CommandButton1_Click()
Cells(1, 1) = "template works"
ThisWorkbook.SaveAs "template-enabled-workbook.xls"
End Sub

thanks

 

[Per Jessen]

Hi

In excel 2007 you need to set the FileFormat option when you use SaveAs:


Private Sub CommandButton1_Click()
'Save as Macro enabled template
Cells(1, 1) = "template works"
ThisWorkbook.SaveAs "template-enabled-workbook.xltm", _
FileFormat:=xlOpenXMLWorkbookMacroEnabled
End Sub


Private Sub CommandButton1_Click()
'Save as Macro enabled workbook
Cells(1, 1) = "template works"
ThisWorkbook.SaveAs "template-enabled-workbook.xltm", _
FileFormat:=xlOpenXMLTemplateMacroEnabled
End Sub


Private Sub CommandButton11_Click()
'Save as Excel 97-2003 workbook
Cells(1, 1) = "template works"
ThisWorkbook.SaveAs "template-enabled-workbook.xls", _
FileFormat:=xlExcel8
End Sub

Regards,
Per