forcing excel sheet to save it as .xlsm

[JackkG]

Hi All,

I want user to save their .xls file to .xlsm format and no other format. I got a piece of code which forces the user to "save as" their file as .xlsm format with no other option available. However, when i tried the code, it runs only for the first time, i mean second time when i open the now .xlsm file, the option doesn't seem to work, cause there can be cases user wants to rename their file or change the location using "save as" and the code doesn't seem to work.

Can someone help me fix this or can guide me how to achieve this? Thank you.

Here is the piece of code i got from Microsoft forum.

-------------------------------



If SaveAsUI = True Then
MsgBox "You have clicked Save As."
Cancel = True
MsgBox "Workbook not saved."
Else
MsgBox "You have clicked Save. Workbook Saved."
End If
'saveas code ends


'action on saveas


Dim txtFileName As String

'1. Check of Save As was used.
If SaveAsUI = True Then
Cancel = True

'2. Call up your own dialog box. Cancel out if user Cancels in the dialog box.
txtFileName = Application.GetSaveAsFilename(, "Excel Macro-Enabled Workbook (*.xlsm), *.xlsm", , "Save As XLSM file")
If txtFileName = "False" Then
MsgBox "Action Cancelled", vbOKOnly
Cancel = True
Exit Sub
End If

'3. Save the file.
Application.EnableEvents = False
ThisWorkbook.SaveAs Filename:=txtFileName, FileFormat:=xlOpenXMLWorkbookMacroEnabled
Application.EnableEvents = True

End If

[GTO]

... i mean second time when i open the now .xlsm file, the option doesn't seem to work, cause there can be cases user wants to rename their file or change the location using "save as" and the code doesn't seem to work.


Hi there,

What do you mean "doesn't seem to work"? Presumably, you have the code snippet in the Workbook_BeforeSave event. If that is the case, I am not seeing anything plain to my eyes at least, that would be amiss. Have you put a Stop near the top of the event procedure and stepped-thru it?

Mark

[JackkG]

Hi GTO,

Yes, its in the same Workbook_BeforeSave event and i didn't place stop anywhere. Is there any other way i can achieve this? Thanks!

[snb]

Private Sub Workbook_BeforeClose(Cancel As Boolean)
with ThisWorkbook
If .fileformat <> 52 Then .SaveAs .Path & CreateObject("scripting.filesystemobject").getbasename(.FullName) & ".xlsm"
end with
End Sub

[GTO]

Yes, its in the same Workbook_BeforeSave event and i didn't place stop anywhere...

Hi Jack,

I was trying to suggest that you place a Stop at the top of the code and then step thru it to see what is happening. In the meantime, please post ALL the code in the Workbook_BeforeSave procedure, or better yet, attach the workbook and let us see if we can replicate the issue.

Mark