prevent Excel 2007 convert to xml offer

JGreig

New Member
Joined
Sep 21, 2009
Messages
5
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
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

Forum statistics

Threads
1,215,606
Messages
6,125,803
Members
449,261
Latest member
Rachel812321

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top