I have created files for users that I want to retain in .xls format so that they are 97-2003 compatible, but the automatic message box offering increased security ("this document is both encrypted and password protected. The Office Open XML formats available in the 2007 release provide stronger encryption. Do you want to increase the security of this document by converting to an Office Open XML Format?") often comes up and inexperienced users (low level computer skills) are saving the files as xlsx and all the macros are deleted.
I've searched everywhere and not found a satisfactory way to prevent the message from within the excel file, or to force saving as only xls.
I found the following code online, but it does not work consistently and can end up in a loop on closing or if the above message comes up and they answer yes. I am not very experienced with code and would really appreciate some advice.
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim fileSaveName As String
Application.EnableEvents = False
Do
fileSaveName = Application.GetSaveAsFilename(fileFilter:="Excel 2003 Files (*.xls), *.xls")
If fileSaveName <> "False" And Right(fileSaveName, 4) <> ".xls" Then
MsgBox ("FILE NOT SAVED AS MACROS WOULD BE LOST - please save in .xls format")
End If
Loop Until fileSaveName = "False" Or Right(fileSaveName, 4) = ".xls"
If fileSaveName <> "False" Then
ThisWorkbook.SaveAs FileName:=fileSaveName
End If
Cancel = True
Application.EnableEvents = True
End Sub
I've searched everywhere and not found a satisfactory way to prevent the message from within the excel file, or to force saving as only xls.
I found the following code online, but it does not work consistently and can end up in a loop on closing or if the above message comes up and they answer yes. I am not very experienced with code and would really appreciate some advice.
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim fileSaveName As String
Application.EnableEvents = False
Do
fileSaveName = Application.GetSaveAsFilename(fileFilter:="Excel 2003 Files (*.xls), *.xls")
If fileSaveName <> "False" And Right(fileSaveName, 4) <> ".xls" Then
MsgBox ("FILE NOT SAVED AS MACROS WOULD BE LOST - please save in .xls format")
End If
Loop Until fileSaveName = "False" Or Right(fileSaveName, 4) = ".xls"
If fileSaveName <> "False" Then
ThisWorkbook.SaveAs FileName:=fileSaveName
End If
Cancel = True
Application.EnableEvents = True
End Sub