Force Saveas Ui To Only Accept Macro Enabled Workbook (xlsm)

less than 1 minute read

Forces the SaveAs UI to only accept a XLSM Macro-Enabled Workbook.

' ## Forces the SaveAs UI to only accept a XLSM Macro-Enabled Workbook
' (1) If a workbook is already saved as xlsx then this code is added then clicking save
' will prompt the user that workbook contains VBA, then bring up the modified SaveAs UI
' (2) A workbook already saved as a xlsm clicking save will keep as xlsm but choosing SaveAs
' will prompt the modified SaveAS UI
' (3) Clicking Save will ignore the below code
' (4) Place in the ThisWorkbook module
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Dim strFileName As String ' Check of Save As was used, if so then proceed If SaveAsUI = True Then Cancel = True ' Open modified Save As dialog box with only xlam file option. ' Cancel out if user Cancels in the dialog box. strFileName = Application.GetSaveAsFilename(, "Excel Macro-Enabled Workbook (*.xlsm), *.xlsm", , "Save As XLSM file") If strFileName = "False" Then Cancel = True Exit Sub End If ' Save the file. Application.EnableEvents = False ThisWorkbook.SaveAs Filename:=strFileName, FileFormat:=xlOpenXMLWorkbookMacroEnabled Application.EnableEvents = True End If
End Sub

Tags: export

Categories: vba

Updated: November 16, 2018

Twitter Facebook LinkedIn

1 minute read

Here’s the simple steps to highlight the row and column of the selected cell which can be extremely useful when navigating large sets of data. Here’s a littl…

less than 1 minute read

How it Works Using SEARCH and ISNUMBER When you need to check if a cell contains specific text (or string) we need to combing the SEARCH and ISNUMBER functio…

less than 1 minute read

So you have a 0% value on one of your data labels and want to hide it?

1 minute read

Download the example workbook here: calculate-time-between-dates.xlsx

Leave a Comment