JackkG
02-22-2016, 03:40 AM
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
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