Excel 2007 Save as .xlsm

[Anne Schouten]

In Excel 2007 I made a template with macros.

If you make a workbook from this template it will, by default, be saved as
workbook without macros (.xlsx)

I would like that the default fileformat will be .xlsm.

I made the following macro to filter the fileformats.

The problem is however that after running this macro the default Save as
dialog box is shown again (the second time without the filter).

How can I prevent that the Save as dialog box is shown twice?





Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)

Dim varFileName

If ActiveWorkbook.Path = "" Then

varFileName = Application.GetSaveAsFilename( _

fileFilter:="Excel workbook with macros
(*.xlsm),*.xslm,Excel workbook (*.xlsx), *.xlsx, Excel 97-2003
(*.xls),*.xls")

ActiveWorkbook.SaveAs Filename:= varFileName

End If

End Sub





Thank you, Anne

 

[Bob Phillips]

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)

Dim varFileName

If ActiveWorkbook.Path = "" Then

varFileName = Application.GetSaveAsFilename( _

fileFilter:="Excel workbook with macros
(*.xlsm),*.xslm,Excel workbook (*.xlsx), *.xlsx, Excel 97-2003
(*.xls),*.xls")

ActiveWorkbook.SaveAs Filename:= varFileName

Cancel = True

End If

End Sub

 

[Anne Schouten]

Bob,

Thank you very much. That is really an easy solution.

Anne

 

[Anne Schouten]

Bob,

I tested your suggestion to add the line Cancel=True.

Just adding this line did not solve the problem, as after the command
ActiveWorkbook.SaveAs the procedure was run again, so I added the line
Application.EnableEvents = False resulting in the code shown below.



It works fine with the command Save, but is the file is closed for the first
time (and it has not been saved before) then for the second time the msgbox
appears to ask whether I want to save the file, at the end of the procedure.
As the file is already saved it does not make any difference which button I
click on, but I think it is rather confusing for the user to get this
question the second time while closing the file.

How can I prevent the second msgbox to appear?



Thanks Anne





Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)

On Error GoTo Er

Dim varWorkbookName, intFileFormat As Integer

Application.EnableEvents = False

If SaveAsUI = True Then

varWorkbookName = Application.GetSaveAsFilename( _

fileFilter:="Excel werkmap met macro's (*.xlsm),*.xslm,Excel
werkmap (*.xlsx), *.xlsx,Excel 97-2003 (*.xls),*.xls")

If varWorkbookName <> False Then

Cancel = True

intFileFormat = ActiveWorkbook.FileFormat

ActiveWorkbook.SaveAs Filename:=varWorkbookName,
FileFormat:=intFileFormat

Else

Cancel = True

End If

End If

Er:

If Err.Number > 0 Then

If Err.Number <> 1004 Then

MsgBox Err.Number & ": " & Err.Description, vbCritical, "Titel"

End If

End If

Application.EnableEvents = True

End Sub