VBA Code for Print Button in Excel (5 Examples)

If we can set a custom Print Button in our Excel worksheet then it can be very user-friendly and time-saving for printing sheets. By using VBA Macros, we can easily do it. So this article will provide you with 5 simple macros to use VBA code for the print button in Excel.


Download Practice Workbook

You can download the free Excel template from here and practice on your own.


5 Examples to Use VBA Code for Print Button in Excel

Let’s get introduced to our dataset first that represents some salesperson’s sales in different Regions.


1. Use VBA Code to Make Print Button for Print Dialog Box in Excel

First, we’ll create a Print Button and then will write codes for it.

Steps:

  • Click as follows: Developer >> Insert >> Button Box.

Soon after, you will get a plus sign (+) in your cursor.

Use VBA Code to Make Print Button for Print Dialog Box in Excel

  • Drag that cursor according to your desired button size and after a while, a dialog box named Assign Macro will open up.

Use VBA Code to Make Print Button for Print Dialog Box in Excel

  • Give a name and press New.

Use VBA Code to Make Print Button for Print Dialog Box in Excel

A VBA window will appear.

  • Then type the following codes in it-
Sub DialogBox()
Application.Dialogs(xlDialogPrint).Show
End Sub
  • Now go back to your sheet.

Use VBA Code to Make Print Button for Print Dialog Box in Excel

Code Breakdown

  • Here, I created a Sub procedure, DialogBox.
  • Then used Dialogs(xlDialogPrint).Show to open the Print dialog box.

The button is created.

  • Right-click on the button and select Edit Text from the Context menu to change the button name.

Use VBA Code to Make Print Button for Print Dialog Box in Excel

  • Later, just type the name and click your mouse anywhere outside of the Button.

Then just press the Button.

Then you will get the Print dialog box. If you want you can Print it now.

If you don’t have any Printer at this moment you can Save it as PDF.  for further use or to Print later.

  • Now press OK.

  • At this moment just give a name and press Save.

Here’s the printed PDF.

Read More: How to Adjust Print Settings in Excel (8 Suitable Tricks)


2. Use VBA Code to Make Print Button for Active Sheet

Here, we’ll use VBA to print an Active Sheet.

Steps:

Use VBA Code to Make Print Button for Active Sheet

Soon after, a VBA window will open up.

  • Then write the following codes in it-
Sub ActiveSheet()
ActiveSheet.PrintOut
End Sub
  • Later, go back to the

Use VBA Code to Make Print Button for Active Sheet

Code Breakdown

  • Here, I created a Sub procedure, ActiveSheet.
  • Then used PrintOut to select the active sheet and print it.

  • Now just press the Button.

Use VBA Code to Make Print Button for Active Sheet

A dialog box named Save Print output As will open up.

  • Give a name and press Save.

Use VBA Code to Make Print Button for Active Sheet

Then you will get the printed PDF.

You can easily change the printer if you need it.

  • Click on File beside the Home

  • Then select Print option and click on the drop-down to select a Printer.

Read More: How to Print All Sheets in Excel (3 Methods)


3. Apply VBA Code to Create Print Button for Selected Sheets in Excel

If you want to Print specific selected sheets then it is also possible using VBA.

Steps:

Apply VBA Code to Create Print Button for Selected Sheets in Excel

Soon after, a VBA window will open up.

  • Later, type the following codes in it-
Sub SelectedSheets()
ActiveWindow.SelectedSheets.PrintOut
End Sub

Then go back to your sheet.

Apply VBA Code to Create Print Button for Selected Sheets in Excel

Code Breakdown

  • Here, I created a Sub procedure, SelectedSheets.
  • Then used ActiveWindow to select sheet from the active Excel window.
  • Next, used PrintOut to print multiple selected sheets.

  • Now select sheets and press the Print Button. I selected two sheets.

Apply VBA Code to Create Print Button for Selected Sheets in Excel

  • Give a name for the PDF and press Save.

The PDF file has two pages for the two sheets.

Read More: How to Print Specific Sheets Using VBA Macro in Excel (4 Ways)


Similar Readings:


4. Embed Excel VBA to Create Print Button for Specific Sheet with Selected Range

Here, we’ll make a Print Button to print a selected range from a specific sheet.

Steps:

Embed Excel VBA to Create Print Button for Specific Sheet with Selected Range

Soon after, a VBA window will open up.

  • Then write the following codes
Sub SpecificSheetnRange()
With Sheets("SpecificSheet+Range")
   .PageSetup.PrintArea = "B2:D11"
   .PrintOut
 End With
End Sub
  • Later, go back to your sheet.

Embed Excel VBA to Create Print Button for Specific Sheet with Selected Range

Code Breakdown

  • Here, I created a Sub procedure, SpecificSheetnRange.
  • Then used With statement to select a specific
  • Next, PrintArea = “B2:D11”.PrintOut will select the range from the specific sheet and will Print it.

  • Now just press the Print Button.

Embed Excel VBA to Create Print Button for Specific Sheet with Selected Range

  • Set a name for the PDF and press Save.

It’s the printed specific sheet.

Read More: Excel VBA: Set Print Area for Multiple Ranges (5 Examples)


5. Embed Excel VBA to Create Print Button for Active Sheet with Selected Range

Also, you can select a range from the active sheet and can make a Print Button to print it using VBA.

Steps:

Embed Excel VBA to Create Print Button for Active Sheet with Selected Range

Soon after, a VBA window will open up.

  • In the VBA window, write the following codes
Sub ActiveSheetnRange()
Range("B2:D11").PrintOut
End Sub
  • Then go back to your sheet.

Embed Excel VBA to Create Print Button for Active Sheet with Selected Range

Code Breakdown

  • Here, I created a Sub procedure, ActiveSheetnRange.
  • Then used Range(“B2:D11”).PrintOut to select a range and print.

  • Press the Print Button.

  • Give a name and press Save.

Then you will get the printed range.

Read More: Excel Button to Print Specific Sheets (With Easy Steps)


Conclusion

I hope the procedures described above will be good enough to use VBA code for the print button in Excel. Feel free to ask any question in the comment section and please give me feedback.


Related Articles

Mithun

Mithun

Hello! I am Md. Sourov Hossain Mithun. Welcome to my profile. Currently, I am working at Exceldemy as an Excel and VBA Content Developer. Excel is amazing software. Here I will post excel related useful articles. I am a graduate of Bangladesh University of Engineering and Technology. I love to learn new things and work with them. Thank you.

3 Comments
  1. 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!

  2. 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.

Leave a reply

ExcelDemy
Logo