How to Use the VBA Code to Create a Custom Print Button in Excel (5 Methods)

Method 1 – Print Dialog Box 

• First, create a button by going to Developer >> Insert >> Button Box.

• Drag the cursor to set the desired button size.

• When the Assign Macro dialog box appears, give your macro a name and press New.

• In the VBA window, type the following code:

Sub DialogBox()
Application.Dialogs(xlDialogPrint).Show
End Sub

• Return to your sheet.

• The DialogBox sub-procedure opens the Print dialog box when the button is clicked.

• Customize the button name by right-clicking on it and selecting Edit Text.

• Press the button to display the Print dialog box.

You can Print immediately or Save as a PDF for later use.

Method 2 – Print Active Sheet

• Follow the first two steps from the previous method to create the button and assign a macro.
• Name the macro and press New.

• In the VBA window, write the following code:

Sub ActiveSheet()
ActiveSheet.PrintOut
End Sub

• Return to your sheet.

• The ActiveSheet sub-procedure prints the active sheet.
• Click the button, and a dialog box named “Save Print Output As” will appear.

• Provide a Name and Save the printed PDF.

To change the printer, follow these steps:

• Click on File next to the Home tab.

• Select the Print option.
• Click on the drop-down menu to choose a printer.

Method 3 – Print Selected Sheets

• Create the button and assign a macro as before (follow the first two-step from the first section).
• Name the macro and press New.

• In the VBA window, type:

Sub SelectedSheets()
ActiveWindow.SelectedSheets.PrintOut
End Sub

• Return to your sheet.

• The SelectedSheets sub-procedure prints the selected sheets.
• Click the button to print the selected sheets.

• Name the PDF and press Save.

Method 4 – Print Specific Sheet with Selected Range

• Create the button and assign a macro as before (follow the first two-step from the first section).

• Assign the following VBA code to the button:

Sub SpecificSheetnRange()
With Sheets("SpecificSheet+Range")
   .PageSetup.PrintArea = "B2:D11"
   .PrintOut
 End With
End Sub

• • Click the button to Print the specified range on the specified sheet.

• Name the PDF and press Save.

Method 5 – Print Active Sheet with Selected Range

• Create the Button and Assign a Macro:
  • Follow the first two steps from the first two-step from the first section to create the button and assign a macro.
  • Name the macro and press New.

• • A VBA window will open.

• Write the VBA Code:
  • In the VBA window, write the following code:

Sub ActiveSheetnRange()
Range("B2:D11").PrintOut
End Sub

• • • This code selects the range “B2:D11” and prints it.

• Return to Your Sheet:
  • Go back to your Excel sheet.

• Test the Button:
  • The ActiveSheetnRange sub-procedure is now associated with the button.
  • Click the button to print the specified range.
  • Provide a name and save the printed output.

• • Provide a name and save the printed output.

Read More: Excel VBA: Print Preview for Selected Range

Download Practice Workbook

You can download the practice workbook from here:

Related Articles

• How to Display Print Preview with Excel VBA
• Excel VBA Debug Print: How to Do It?
• Excel VBA: How to Set Print Area Dynamically
• How to Set Print Area to Selection Using VBA in Excel
• Excel VBA: Set Print Area for Multiple Ranges
• Excel VBA: Print Range of Cells
• Excel VBA to Display Print Preview for Multiple Sheets