Force users to save as .xlsm or pdf only

[Mcoca]

Hello everyone,

This thread is related and inspired on an old one: Force users to save as .xlsm or xltm only.

In any case, I tried to replicate the code for XLSM and PDF format only. No luck. Although the two options are available, the PDF one didn't produce any good file. I would like to ask you if it is possible to make this code work. Thank you very much!

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

Dim txtFileName As String

If SaveAsUI = True Then
Cancel = True
txtFileName = Application.GetSaveAsFilename(fileFilter:="Excel Macro-Enabled Workbook (*.xlsm), *.xlsm, PDF Files (*.pdf), *.pdf", Title:="Save As...")

If txtFileName = False Then
MsgBox "Action Cancelled", vbOKOnly
Cancel = True

Exit Sub
End If

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

End Sub

 

[davesexcel]

I'm just putting this out there.
You cannot save a workbook as a PDF.

You should control what & where the workbook is saved as, instead of using the getsaveas dialog box, it will be quite annoying very quickly.