Force Save as PDF

[Chris_010101]

Hi All,

I know this is not necessarily Excel-related, however, it is going to be VBA related.

I have created a return-to-work form in word, which is sent to managers to complete return-to-work interviews for sick employees.

The document is restricted and only form fields can be completed. However, what I would like to do is force the user to save as a PDF, at the 'Save As' screen, so that they can ONLY save it as a PDF.

Basically, this will hopefully stop the completed form being accidentally overwritten later.

Is this even possible?

Kind Regards
Chris

 

[Candyman8019]

There are a couple of ways to do this...if you're only trying to keep the form from being overwritten, save it as an .xltm
When the user opens the .xltm (template), Excel will add a character to the filename to ensure it doesn't overwrite the template.


If you do want to save as .PDF, you can use the Workbook_beforesave event. When the user clicks the save button, it will prompt them to save as PDF with no other options. For example, try this code in the thisWorkbook module:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim fName As String
If SaveAsUI <> False Then
    Cancel = True
    fName = Application.GetSaveAsFilename(, "PDF (*.PDF), *.PDF", , "Save As PDF file")
    If fName <> "False" Then
      Application.EnableEvents = False
      ActiveWorkbook.SaveAs Filename:=fName, FileFormat:=xlTypePDF
      Application.EnableEvents = True
    Else
      MsgBox "Cancelled.  File not saved."
      Cancel = True
      Exit Sub
    End If
End If
End Sub

 

[Chris_010101]

There are a couple of ways to do this...if you're only trying to keep the form from being overwritten, save it as an .xltm
When the user opens the .xltm (template), Excel will add a character to the filename to ensure it doesn't overwrite the template.


If you do want to save as .PDF, you can use the Workbook_beforesave event. When the user clicks the save button, it will prompt them to save as PDF with no other options. For example, try this code in the thisWorkbook module:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim fName As String
If SaveAsUI <> False Then
    Cancel = True
    fName = Application.GetSaveAsFilename(, "PDF (*.PDF), *.PDF", , "Save As PDF file")
    If fName <> "False" Then
      Application.EnableEvents = False
      ActiveWorkbook.SaveAs Filename:=fName, FileFormat:=xlTypePDF
      Application.EnableEvents = True
    Else
      MsgBox "Cancelled.  File not saved."
      Cancel = True
      Exit Sub
    End If
End If
End Sub

Hello,

I'm not too fussed about the template being overwritten, as the word document is downloaded from SharePoint by the user.

I've added your code but it's not working for me. Can I check that I'm adding it in the correct place?

Developer > Visual Basic

When 'Save As' it gives the drop-down list and all the file types.
When 'Save' it overwrites the current document in the .docm format.

Thanks

 

[Candyman8019]

My apology…this is for Excel, not word.

 

[Chris_010101]

My apology…this is for Excel, not word.

Kinda my fault, to be fair. I'm raising a word query on an excel forum.