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

Method 1 – Print Dialog Box

Create a button by going to Developer, select Insert and click on the 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, enter 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, enter 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, enter:

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

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.
In the VBA window, enter the following code:

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

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

Go back to your Excel sheet.
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.

Download Practice Workbook

You can download the practice workbook from here:

VBA Code to Create Print Button.xlsm

Get FREE Advanced Excel Exercises with Solutions!

5 Comments

Andy S. Jun 13, 2022 at 4:32 PM

Excellent article. The next question is: I have an Excel sheet that utilizes the same layout and info, the only thing that needs changed is the information from one single drop down list which includes 13 locations (lines of information) that is stored on the separate “Data” sheet. How can I make one print button that would print each individual page with each location from the list or items in the drop down box? Please let me know if you can be of any assistance with this issue. Your help is greatly appreciated!
- Reply
  Pascal Ernest Jul 20, 2022 at 11:08 PM
  
  Hallo, am Pascal
  I have a project in Excel can you help be how to use VBA to automate it?
Mithun Jul 19, 2022 at 12:26 PM

Hello ANDY S, thanks for your feedback.
I hope the following codes will be helpful for your problem.

Sub Print_Button_for_DropDown()

Sheets(“Data”).Range(“$B$4:$D$11”).AutoFilter Field:=2, Criteria1:=Range(“F4”).Value
Sheets(“Data”).Select
Sheets(“Data”).PrintOut

End Sub

Here, I have made a drop-down list in Cell F4 for the locations. Keep this cell in that sheet where the print button is located, that means the active sheet. You can change the reference and range in the codes according to your dataset.
Grace Sep 26, 2024 at 4:26 AM

Hello I have question if is possible to connect spin button witch print button (spin button will shown quantity of labels from next sheet)
1st Sheet will be Data then next shhet will be with name of label. But on front sheet I want quickly add extra copies. My question is there is possible to join spin button with qty and print button.
Thanks for any clue.
Grace.
- Reply
  Shamima Sultana Sep 26, 2024 at 12:17 PM
  Hello Grace,
  
  It is possible to connect a spin button with a print button. The spin button can adjust the quantity of labels from the next sheet, and the print button can trigger printing based on the value selected. You would need to use a combination of VBA code to link the spin button’s value to the print process, specifying the number of copies using the spin button control.
  
  First, insert the Spin Button and assign it a cell for its value (e.g., Sheet1!A1).
  Then, add the Print Button and assign the following VBA code:
```
Private Sub PrintButton_Click()
    Dim qty As Integer
    qty = Sheet1.Range("A1").Value ' Get spin button value
    
    For i = 1 To qty
        Sheets("LabelsSheet").PrintOut
    Next i
End Sub
```
  This code reads the quantity from the spin button and prints the label sheet that many times.
  
  Regards
  ExcelDemy