Md. Sourov Hossain Mithun
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:
Soon after, you will get a plus sign (+) in your cursor.
- Drag that cursor according to your desired button size and after a while, a dialog box named Assign Macro will open up.
- Give a name and press New.
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.
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.
- 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:
- Follow the first two-step from the first section to create the button and Assign a Macro.
- Write a Macro Name and press New.
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
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.
A dialog box named Save Print output As will open up.
- Give a name and press Save.
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:
- Follow the first two-step from the first section to create the button and assign a macro.
- Then write a Macro Name and press New.
Soon after, a VBA window will open up.
- Later, type the following codes in it-
Sub SelectedSheets()
ActiveWindow.SelectedSheets.PrintOut
End SubThen go back to your sheet.
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.
- 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:
- How to Print Excel Sheet in A4 Size (4 Ways)
- How to Keep Header in Excel When Printing (3 Ways)
- Excel VBA: How to Set Print Area Dynamically (7 Ways)
- How to Print Excel Sheet with Lines (3 Easy Ways)
- How to Print Graph in Excel (5 Ways)
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:
- Follow the first two-step from the first section to create the button and assign a macro.
- Write a Macro Name and press New.
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.
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.
- 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:
- Follow the first two-step from the first section to create the button and assign a macro.
- Later, write a Macro Name and press New.
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.
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.
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!
Hallo, am Pascal
I have a project in Excel can you help be how to use VBA to automate it?
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.