VBA/macro to Limit Save As File Options

I have been searching and searching for some codes that would do what I want, and I just can't seem to find the answer.

I am creating macro-enabled templates for office use on a network server.

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 code that made it save as .xlsm only. BUT I want to also add PDF to the list. So, I ONLY want .xlsm and PDF as the options to choose from.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    If Right(ThisWorkbook.Name, 4) <> "xlsm" Then
        Dim txtFileName As String, opt As Variant
        opt = MsgBox("If you have made changes to this workbook must be saved as a macro enabled workbook." & vbLf & _
        "If you have made no changes and want to exit without saving, click the 'YES' button", vbYesNo + vbExclamation, "EXIT OPTION")
            If opt = vbYes Then
                Cancel = False
                Exit Sub
            End If
        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

How I want "Save As..." Options to Look

I'm a newbie at coding, I tried to add the PDF file extension here:

txtFileName = Application.GetSaveAsFilename(ThisWorkbook.FullName, "Excel Macro-Enabled Workbook (*.xlsm), *.xlsm", , "Save As XLSM file")

But, I was unsure how to accurately add it.

Solution

Adjust your code as follows:

Dim intPos As Integer
Dim strWorkBookName as string

' Remove the file extension so that the file name itself defaults to the
' first filter extension
intPos = InStrRev(strWorkBookName, ".")
If intPos <> 0 Then
  strWorkBookName = Left(strWorkBookName, intPos - 1)
End If

' Prompt for XLSM or PDF file name
txtFileName = Application.GetSaveAsFilename(InitialFileName:=strWorkBookName, _
                                            FileFilter:="Excel Macro-Enabled Workbook (*.xlsm), *.xlsm, PDF File (*.pdf),*.pdf", _
                                            Title:="Save As XLSM or PDF file")

You will also need to adjust your SaveAs code to reflect your user's selection. And to preempt your next question, you can save as a PDF file using the following code:

ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, Filename:=strWorkBookName