Disable print but allow PDF printing

I'm trying to set up a spreadsheet so that it cannot be physically printed, but I have a macro button to save as a PDF which I need to work.

I've used the following codes:

Sub SaveQuoteAsPDF()
  
  
Dim fName As String
With Worksheets("Quote")
     fName = Range("E21").Value & Format(Now, " ddmmyy")
End With
 ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, FileName:= _
             "S:\SALES\Margin Calculator\Quotes\" & fName, Quality:=xlQualityStandard, _
             IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False

Private Sub Workbook_BeforePrint(Cancel As Boolean)
'Update 20140626
Cancel = True
MsgBox "You can't print this workbook"

End Sub

both work indivually but together the code to disable printing also stops the PDF from being written.

Is there a way to overcome this? I do have another tab that saves as a PDF as well that follows the same code, and as I have looked the workbook this also cannot be written to a PDF. I'm happy to only lock one worksheet, but I struggled to make this work...

This works great thanks.

I have 2 other worksheets that need to follow the same rule, but the macro print button isn't in the same place, (roughly F9) and it takes the cell name from E21.

This says it subscript out of range so how can I change the print range for these macros? I don't really understand how the range ("A10")makes it work?

This works great thanks.

I have 2 other worksheets that need to follow the same rule, but the macro print button isn't in the same place, (roughly F9) and it takes the cell name from E21.

This says it subscript out of range so how can I change the print range for these macros? I don't really understand how the range ("A10")makes it work?

It is nothing to do with where the print button is.
Choose a cell, any cell, anywhere in the workbook, on any worksheet. A cell that is away from your data.
The same cell can be used for all the worksheets.
The cell is used to either hold the words "PRINT PDF" or to be blank.

The new line(below) in Sub SaveQuoteAsPDF places the words "PRINT PDF" in that cell

Sheets("Sheet1").Range("A10") = "PRINT PDF"

When you run SaveQuoteAsPDF we need to stop the BeforePrint from cancelling your PDF report and we do this by putting a condition in the BeforePrint macro, telling it to look at the value of the cell that may (or may not) contain the words "PRINT PDF". The condition is in this in line:

If Sheets("Sheet1").Range("A10") = "PRINT PDF" Then

If the condition is satisfied (ie the cell contains "PRINT PDF") then it bypasses the "cancel" print control

The next line in the same macro re-instates the value in the cell to blank, stopping anyone from printing again

Sheets("Sheet1").Range("A10") = ""

In summary when your run the PDF macro it temporarily changes the value of one cell to include the words "PRINT PDF" and it is then changed back to blank again by the BeforeClose macro.
Hope this helps.

Thanks,

This makes a lot more sense now, though I am still having trouble getting it to work on the other 2 sheets.

I chose A7 as that cell is empty on each sheet, and changed the code for each Print PDF macro, but how do I get it all into the cancle print macro?

I've tried a few variations but nothing I have done seems to work. I can't have separate macros for each sheet as the name is ambiguous, and I'm not sure how to get each sheet into one macro.

The sheets are:
Sheet1
Sheet14
Sheet15

Thanks!

Only use one cell - NOT one in each worksheet.


The pdf Macro for sheet1 should contain the line

Sheets("Sheet1").Range("A7") = "PRINT PDF"

The pdf Macro for sheet14 should contain EXACTLY THE SAME the line

Sheets("Sheet1").Range("A7") = "PRINT PDF"

The pdf Macro for sheet15 should contain EXACTLY THE SAME the line

Sheets("Sheet1").Range("A7") = "PRINT PDF"

and, finally, the BeforePrint macro should contain the line

Sheets("Sheet1").Range("A7") = "PRINT PDF"

Thanks SO much, this has solved it!