psctornado
03-13-2014, 12:07 PM
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?? :think:
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
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?? :think:
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