Excel Tips: How to Add Printing Button in a Worksheet - one for Print to PDF and another for Print to Device

Problem: How can you add a button for PDF printing and another for printing in device. 

Solution: You can add buttons for PDF printing and printing directly to a device in Excel by using VBA. Here's how to create these buttons and assign the necessary macros:

1. Enable the Developer Tab (if it's not already enabled):

   • Go to File > Options.
   • Select Customize Ribbon.
   • In the right pane, check the Developer checkbox.
   • Click OK.

2. Add Buttons to the Worksheet:

   • Go to the Developer tab.
   • Click Insert in the Controls group.
   • Under Form Controls, click the Button (Form Control) icon.
   • Click and drag on the worksheet to draw the button.
   • Repeat to create two buttons, one for PDF printing and one for direct printing.

3. Assign Macros to the Buttons:

   • After drawing each button, the Assign Macro dialog will appear.
   • Click New to create a new macro for each button, which will open the VBA editor.

4. Write the VBA Code:

   • In the VBA editor, you will see something like this:

     vba
     Sub Button1_Click()
     '
     ' Button1_Click Macro
     '
     ' 
     End Sub
     

   • Modify the code for the PDF printing button:

     vba
     Sub PrintToPDF()
         Dim ws As Worksheet
         Dim pdfPath As String
         
         ' Set the path where the PDF will be saved
         pdfPath = ThisWorkbook.Path & "\MyWorksheet.pdf"
         
         ' Export the active sheet to PDF
         ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=pdfPath, _
             Quality:=xlQualityStandard, IncludeDocProperties:=True, _
             IgnorePrintAreas:=False, OpenAfterPublish:=True
     End Sub
     

   • Modify the code for the direct printing button:

     vba
     Sub PrintToPrinter()
         ' Print the active sheet
         ActiveSheet.PrintOut
     End Sub
     

   • Close the VBA editor.

5. Assign the Macros to the Buttons (if not automatically assigned):

   • Right-click the PDF printing button.
   • Select Assign Macro.
   • Choose the macro you created for PDF printing (e.g., PrintToPDF).
   • Click OK.
   • Repeat for the direct printing button, assigning it the macro for direct printing (e.g., PrintToPrinter).

Now, you will have two buttons on your worksheet. Clicking the PDF button will save the active sheet as a PDF file, and clicking the print button will print the active sheet directly to your default printer.