Macro-Enabled Template VBA Code for Saving Workbook

[letswriteafairytale]

So, I have macro-enabled templates for office use. I found a VBA code to KINDA address the problem I was having.

Not everyone here is very Excel savvy, so I know they wouldn't be able to remember to save the workbooks as macro-enabled workbook.

I found the code for that:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
If Right(ThisWorkbook.Name, 4) <> "xlsm" Then
Dim txtFileName As String
MsgBox "This workbook must be saved as a macro enabled workbook"
Cancel = True
txtFileName = Application.GetSaveAsFilename(ThisWorkbook.FullName, "Excel Macro-Enabled Workbook (*.xlsm), *.xlsm", , "Save As XLSM file")
If txtFileName = "False" Then
MsgBox "Action Cancelled", vbOKOnly
Cancel = True
Exit Sub
End If
Application.EnableEvents = False
ThisWorkbook.SaveAs Filename:=txtFileName, FileFormat:=52 'xlOpenXMLWorkbookMacroEnabled
Application.EnableEvents = True
End If
End Sub




We save these as PDF's as well. So, when they're finished with the workbook, we will save as PDF. The problem with this code is it ONLY allows .xlsm.... I am no coding expert so, what do I add to the code to allow the save as PDF, as an option as well?





Thanks!

 

[Logit]

You'll need to add the following code. Edit the path as desired for your location.

'Create and assign variables
Dim saveLocation As String
saveLocation = "C:\Users\marks\OneDrive\Documents\myPDFFile.pdf" '... <-- edit path here

'Save active workbook as PDF
ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, _
    Filename:=saveLocation