SaveasUI - Save as .xls not XLSM

[psctornado]

Hi All,

I currently have a workbook that is in .xlsm format, however what I would like is when the user hits 'save as' that the default option is .xls (97 - 2003 format). I need to save in this format since the location where I save the spreadsheet requires it to be in .xls or xlsx format.

Through my travels on the interweb, I found the saveasui command. It seems however, that when I place the code in the 'This Workbook' section of VBA, that the default is set to .xls, but once I save the file another prompt comes up for .xlsm. Is there a way to surpress this so that only the save as prompt comes up with .xls??

Any help would be greatly appreciated.

Thanks!

Below is the code that I currently have.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim varWorkbookName As String
Dim FileFormatValue As Integer
On Error GoTo Quit
Application.EnableEvents = False
If SaveAsUI = True Then
varWorkbookName = Application.GetSaveAsFilename( _
fileFilter:="Excel 97-2003 Workbook (*.xls), *.xls")
End If
Cancel = True
Quit:
If Err.Number > 0 Then
If Err.Number <> 1004 Then
MsgBox "Error: " & Err.Number & Err.Description & vbCrLf & vbCrLf & vbCrLf & _
"Title", vbCritical
End If
End If
Application.EnableEvents = True
End Sub

[snb]

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Application.DefaultSaveFormat = 56
end sub

[psctornado]

Hi SNB,

Looks like the above code still prompts for the .xlsm file format as default. I susbstituted my original code with yours and it doesn't get me the .xls as the 56 save default value would normally.

Any other thoughts???