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.
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.
Define the variables.
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.
It adds all the sheets into the Sheet_List variable.
Related Content: How to Print Selected Area in Excel (2 Examples)
- 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
Dim n As Long, m As Long Dim array_1() As String
Define the variables.
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.
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.
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.
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.
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.
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.