Prevent printing by excel normal feature & save worksheet in pdf by a command button

[Atiqul Haque]

Dear All,

I want to prevent printing of worksheet by excel normal feature and allow only to save and print the worksheet as pdf by a command button.
Would you pls solve this problem for me. Your help will be highly appreciated.

i use the following code's but unfortunately when i put the prevent print code in workbook module i can't make the workbook saved as pdf.

Code in workbook module:
Private Sub Workbook_BeforePrint(Cancel As Boolean)
MsgBox "Please print via [Print] or [Pdf] button"
Cancel = True
End Sub

Code in Command Button:
Sub createPDF()
Sheet2.Range("A7:M37").ExportAsFixedFormat xlTypePDF, Filename:= _
"C:\Users\User\Desktop\Save File As PDF\" & Range("C9") & "-" & Range("C10") & "_" & Range("I10") & "_" & "Biochemical_Report", _
OpenAfterPublish:=True
End Sub

 

[Fluff]

Hi & welcome to MrExcel.
In a standard module put

Public AllowPrint As Boolean

It must go at the very top of the module, before any code.
Then in the Thisworkbook module use

Private Sub Workbook_BeforePrint(Cancel As Boolean)
   If AllowPrint Then Exit Sub
   MsgBox "Please print via [Print] or [Pdf] button"
   Cancel = True
End Sub

and your print button

Sub createPDF()
   AllowPrint = True
   Sheet2.Range("A7:M37").ExportAsFixedFormat xlTypePDF, Filename:= _
      "C:\Users\User\Desktop\Save File As PDF\" & Range("C9") & "-" & Range("C10") & "_" & Range("I10") & "_" & "Biochemical_Report", _
      OpenAfterPublish:=True
   AllowPrint = False
End Sub

 

[Atiqul Haque]

Hi & welcome to MrExcel.
In a standard module put

Public AllowPrint As Boolean

It must go at the very top of the module, before any code.
Then in the Thisworkbook module use

Private Sub Workbook_BeforePrint(Cancel As Boolean)
   If AllowPrint Then Exit Sub
   MsgBox "Please print via [Print] or [Pdf] button"
   Cancel = True
End Sub

and your print button

Sub createPDF()
   AllowPrint = True
   Sheet2.Range("A7:M37").ExportAsFixedFormat xlTypePDF, Filename:= _
      "C:\Users\User\Desktop\Save File As PDF\" & Range("C9") & "-" & Range("C10") & "_" & Range("I10") & "_" & "Biochemical_Report", _
      OpenAfterPublish:=True
   AllowPrint = False
End Sub

Thank you very much sir. Now it's work perfectly.

 

[Fluff]

You're welcome & thanks for the feedback.

 

[Atiqul Haque]

S

You're welcome & thanks for the feedback.

Sir one more thing,
can i put print command directly to the printer? What i mean that the file will be saved as pdf w/out showing the pdf file but go directly to the printer to make print.

Thanks in advance.

 

[Fluff]

Just change OpenAfterPublish: to False in-stead of True

 

[Atiqul Haque]

Just change OpenAfterPublish: to False in-stead of True

Dear Sir thank you very much for your quick reply. To use this code pdf file do not show but not go for printing directly. I need w/out showing the pdf file, file goes to the printer directly to print. Is it possible?
Thank you in advance.

 

[Fluff]

Do you want to print the file, or save it as a pdf?

 

[Atiqul Haque]

Do you want to print the file, or save it as a pdf?

Thank you very much for your reply.
Actually i want to print but also wants to save it in pdf format without showing the pdf file with a single command button. Is it possible sir?
Thanks in advance.

 

[Fluff]

You can use the code you already have & then add something to the end to print the sheet.
Just use the macro recorder to record yourself printing the sheet & you will have that code.