limiting Excel save as extensions

[AHarvey]

Hi all,

I've got a work spreadsheet that needs to be edited by some other people who are less competent (read "retarded") and then saved as a .xlsm file.

They open it as a .xlsm file, but seem to constantly be saving as a .xlsx instead, despite numerous emails and adding a massive header the sheet telling them so.

So, my question, is there any code I can use to make it impossible to save as anything but an .xlsm file?

EDIT: PLEASE SEE POST #3 for a new issue

 

[AHarvey]

Typical, spend 30 mins googling and find nothing.

Post here.

Find something online

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim fName As String

fName = Application.GetSaveAsFilename(, "Excel Macro-Enabled Workbook (*.xlsm), *.xlsm")
If fName = "False" Then
MsgBox "You pressed cancel", vbOKOnly
Cancel = True
End If

Application.EnableEvents = False
ThisWorkbook.SaveAs Filename:=fName, FileFormat:=xlOpenXMLWorkbookMacroEnabled
Application.EnableEvents = True

End Sub

 

[AHarvey]

Ok, small issue with the above code.

if you click save and click cancel you get a msgbox "you pressed cancel".

It then automatically saves it as False.xlsm. Anyway to stop it?

 

[GravyMonster]

I'm no Excel macro expert, but try this:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
	Dim fName As String

	fName = Application.GetSaveAsFilename(, "Excel Macro-Enabled Workbook (*.xlsm), *.xlsm")
	If fName = "False" Then Exit Sub

	Application.EnableEvents = False
	ThisWorkbook.SaveAs Filename:=fName, FileFormat:=xlOpenXMLWorkbookMacroEnabled
	Application.EnableEvents = True
End Sub

The new 'Exit Sub' part should stop the execution of the following code which does the saving of the worksheets.