We perform a variety of operations in Microsoft Excel. All the operations are based on data. We store, process, and get a result from those data. Instead of different operations on data, Excel has other tools also. To print sheets with data is one of them. We can print sheets by different processes. In this article, we will learn to print specific sheets using an Excel Button.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
How to Create an Excel Button to Print Specific Sheets
There are three different sheets in our reference Excel file. We will print specific sheets from that file using the Excel button.
Steps to Create a Button to Print Particular Excel Sheets
First, we need to create a list of sheets using the ActiveX Controls. Then create an Excel button using the Form Control section. Finally, perform the print operation by pressing the Print Button. We will select our desired sheets from the list, then print them. Here, we will discuss each step of this process to create this print button and how to print specific sheets using this print button.
Step-1: Add Developer Tab in Excel
In Excel, we add control buttons from the Insert option of the Developer tab. The Developer tab is not available in Excel initially. So, we add the Developer tab by customizing the ribbon section.
- Go to File > Options.
- Select Customize Ribbon option of the Excel Options window.
- Then, mark the Developer option.
- Finally, press OK from the bottom of the window.
The Developer tab has been added to the Ribbon section.
Read More: Print Multiple Excel Sheets to Single PDF File with VBA (6 Criteria)
Step-2: Create a List Using ActiveX Controls
In this section, we will show how to create a list of Excel sheets.
- Now, go to the Developer tab.
- Click on the Insert option.
- Now, choose the List Box option from the ActiveX Controls.
- Move the cursor to any blank space of the dataset.
- Now, press the right button of the mouse.
- Choose Properties from the Context Menu.
- Properties window appears now.
- Modify the (Name) section to Sheet_List.
- Go to the MultiSelect option drop-down list and choose the 2 – fmMultiSelectExtended Then save the changes.
2 – fmMultiSelectExtended option offers to select multiple cells using the Ctrl or Shift buttons.
Related Content: Print Multiple Excel Sheets to Single PDF File with VBA (6 Criteria)
Step-3: Customize the ActiveX Box
In this section, we will customize the ActiveX Control box for our use.
- Go to the dataset and move the cursor on the Control Box.
- Press the right button of the mouse again.
- Choose the View Code option from the Context Menu. We can also press Alt+F11 for this.
- We enter the VBA Choose the Worksheet option from the Object drop-down list.
- There is SelectionChange on the default Event on the right side of the VBAÂ window.
- But we change the Event to Active.
- Now, put the following VBAÂ code.
Private Sub Worksheet_Activate()
Dim Sheet_Name
Me.Sheet_List.Clear
For Each Sheet_Name In ThisWorkbook.Sheets
Me.Sheet_List.AddItem Sheet_Name.Name
Next Sheet_Name
End Sub
- Run the VBA code by pressing the F5Â button.
We can see that sheet names are shown as a list.
Code Explanation:
Dim Sheet_Name
Define the variables.
Me.Sheet_List.Clear
It clears all the previous values from the Sheet_List variable.
For Each Sheet_Name In ThisWorkbook.Sheets
This selects each sheet of the Excel file.
Me.Sheet_List.AddItem
It adds all the sheets into the Sheet_List variable.
Related Content: How to Print Selected Area in Excel (2 Examples)
Similar Readings:
- How to Print Labels in Excel (Step-by-Step Guideline)
- Center the Print Area in Excel (4 Ways)
- How to Print Full Page in Excel (4 Simple Ways)
- Print Gridlines in Excel (2 Ways)
- How to Print Data with Excel VBA (A Detailed Guideline)
Step-4: Add Custom VBA Code to Print Sheet
In this section, we will add a customized VBA code to print sheets.
- Choose the Module option from the Insert tab.
- Copy and paste the following VBA code on the module.
Sub print_specific_sheets()
Dim n As Long, m As Long
Dim array_1() As String
With ActiveSheet.Sheet_List
For n = 0 To .ListCount - 1
If .Selected(n) Then
ReDim Preserve array_1(m)
array_1(m) = .List(n)
m = m + 1
End If
Next n
Sheets(array_1()).PrintOut
End With
End Sub
Code Explanation:
Dim n As Long, m As Long
Dim array_1() As String
Define the variables.
With ActiveSheet.Sheet_List
Select the active sheet of the Sheet_List variable.
For n = 0 To .ListCount - 1
Apply a for loop from 0.
If .Selected(n) Then
               ReDim Preserve array_1(m)
               array_1(m) = .List(n)
               m = m + 1
The If function is applied with the VBA ReDim function. This ReDim function is used to resize a dynamic array.
    Sheets(array_1()).PrintOut
This command is used to print out a sheet.
Read More: How to Print All Sheets in Excel (3 Methods)
Step-5: Disable Design Mode
After completing all the tasks of the ActiveX button, we disable the design mode. If we do not disable it, this ActiveX button will be in editable mode.
- To disable this, just click on the Disable Mode from the Developer tab.
Read More: Print Titles in Excel Is Disabled, How to Enable It?
Step-6: Create a Control Button for Print
In this section, we will create a control button to print the specific sheets from the list.
- Select the Insert option from the Developer tab.
- Choose a Button from the Form Controls option.
Read More: VBA Code for Print Button in Excel (5 Examples)
Final Step: Assign the Macro to the Print Button
- Move the cursor to any blank space of the dataset. The Assign Macro pop will show. Click on OK.
- We can see a button has been added to the dataset. Edit the button and change the name to Print Button.
- Now first, click on any of the Sheets from the list and click on Print Button.
A pop mentioning the printing command is showing here.
In this way first, we will choose sheets from the list and then press the Print Button. And finally, we will get our desired sheet to be printed.
Read More: How to Display Print Preview with Excel VBA (3 Macros)
Add an Icon to Print Specific Excel Sheet
Instead of adding a button with text naming, we can add icons for print sheets.
- Choose the Illustration option from the Insert tab.
- Now, click on Icons and choose our desired icon.
- Now, place the icon of the data set and assign the macro as shown previously.
This Icon will work exactly like the Print Button. Just, choose the sheet from the list and click on this icon.
Conclusion
In this article, we described the total process to create an Excel Button to print specific sheets step by step in detail. I hope this will satisfy your needs. Please have a look at our website Exceldemy.com and give your suggestions in the comment box.
Instead of printing, how can we adjust the VBA code to save the selected sheet in the list as a PDF in a location of our choice?
Hi GABOR! Please click on the following link to get a code that will help you to save selected worksheets in the list as PDF files in a suitable location.
https://www.exceldemy.com/vba-code-for-print-button-in-excel/#3_Apply_VBA_Code_to_Create_Print_Button_for_Selected_Sheets_in_Excel
Note that, you have to go through the first two steps of the first example in the given article. Then you can apply the code there. (We have mentioned it there also.)
Stay with us. Best regards.
Hi!
Anyway to create a locked list, instead of generating one? Every time I open the workbook, my list is empty.
Hello SAM,
We have attached an Excel file with this reply which you can download from the link below.
Print-Sheets-Using-Excel-Button.xlsm
Here, we’ve modified the code such that it automatically lists the names of the worksheets when opened. You can select multiple worksheets and press the print button to print them.
Regards,
ExcelDemy
Thank you for the code it works as indicated. How can I print multiple sheets (30)?
Hi MARK,
Thanks for your comment. To learn different ways to print multiple sheets, you can follow this article of ExcelDemy: Print multiple sheets in Excel
If you want to print multiple sheets using VBA only, then follow this one: Print multiple Excel sheets to single PDF with VBA
Hope you will find this helpful.
Regards
Rafiul Hasan
Team ExcelDemy
Hello,
I would like to do something more;
1- Can we create a list for all visible sheets instead of all sheets from workbook?
2- I would like to pick the printer after the press “print” button. Can you show me how can I do?
Thank you CANER for your queries.
1. If you want to create a list for only visible sheets, you need to modify the Worksheet_Activate() subroutine in the following way:
Here, I have added an extra IF statement so that the code only adds the sheets that are visible to the list.
2. If you want to choose the printer before printing, you need to modify the print_specific_sheets subroutine in the following way:
Here, I only added the following line to show the Printer Setup dialogue box from where you can choose from available printers.
I hope it solves your problem.
Regards
Aniruddah
Team Exceldemy