I'm trying to use VBA to force a situation where the worksheets of an active Excel workbook will only print as PDF documents - in other words, all printing options other than printing as a PDF file are disabled, and hitting the "Print As" button or "Quick Print" buttons automatically print the document in PDF format. Alternatively, I woudl consider disabling all printing functions and requiring the user to save the file as a PDF document (I'm not sure if this accomplishes the same thing). I'm working through Chandoo's Introduction to VBA classes now (started in September 2011 and haven't been able to finish them yet), so I'm new to VBA programming. Does anyone have any advice or guidance to help me solve this problem?
Use VBA to force printing as PDF
- Thread starter skdyck
- Start date
SirJB7
Excel Rōnin
Hi, skdyck!
You'll have to get involved in some VBA code.
For example, you should intercept standard Excel built-in subs's events or dialogs as:
Workbook_BeforePrint, Workbook_BeforeSave, and so on.
For each one of them, you should include pieces of code like this:
-----
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Application.EnableEvents = False
ActiveWorkbook.SaveAs FileName, FileFormat, Password, WriteResPassword
Application.EnableEvents = True
End Sub
-----
Where you set/restrict by code the possible values for each parameter (only the needed ones: for details of all parameters, press F1 over the SaveAs string text from within VB editor).
Same for printing...
But if the users can update, modify, change or do anything other than print & save, you should leave open the door of saving it as an Excel file too... otherwise they're going to start a hunting party with you.
Regards!
You'll have to get involved in some VBA code.
For example, you should intercept standard Excel built-in subs's events or dialogs as:
Workbook_BeforePrint, Workbook_BeforeSave, and so on.
For each one of them, you should include pieces of code like this:
-----
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Application.EnableEvents = False
ActiveWorkbook.SaveAs FileName, FileFormat, Password, WriteResPassword
Application.EnableEvents = True
End Sub
-----
Where you set/restrict by code the possible values for each parameter (only the needed ones: for details of all parameters, press F1 over the SaveAs string text from within VB editor).
Same for printing...
But if the users can update, modify, change or do anything other than print & save, you should leave open the door of saving it as an Excel file too... otherwise they're going to start a hunting party with you.
Regards!
You could try putting all of this in the ThisWorkbook module. The idea is to use the Before_Print event to capture the users action and force a pdf print, and then cancel the regular print.
[pre]
[/pre]
[pre]
Code:
Private Sub Workbook_BeforePrint(Cancel As Boolean)
Call PrintPDF
'We already saved the file we want, so cancel the application print
Cancel = True
End Sub
Private Sub PrintPDF()
Dim MyName As String
'Set the starting directory to wherever we are currently
ChDir ThisWorkbook.Path
MyName = SaveDialogue
'If user cancels, escape
If MyName = "" Then Exit Sub
MyName = Trim(Left(WorksheetFunction.Substitute(MyName, ".xls", WorksheetFunction.Rept(" ", 999)), 999))
MyName = MyName & ".pdf"
ThisWorkbook.SaveAs Filename:= _
MyName, FileFormat:= _
xlExcel8, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
, CreateBackup:=False
End Sub
Private Function SaveDialogue()
'Opens the save dialogue for user to choose
'correct folder & name ot use
Dim sFolderName As String, fDialog As FileDialog, ret As Long
Set fDialog = Application.FileDialog(msoFileDialogSaveAs)
ret = fDialog.Show
If ret <> 0 Then
sFolderName = fDialog.SelectedItems(1) & Application.PathSeparator
SaveDialogue = sFolderName
Else
'MsgBox "User pressed cancel"
End If
Set fDialog = Nothing
End Function