Excel Button to Print Specific Sheets: 7 Easy Methods

There are three different sheets in our reference Excel file. We will print specific sheets from that file using the Excel button.

Method 1 – Add the Developer Tab in Excel

• Go to File > Options.
• Select Customize Ribbon of the Excel Options window.
• Mark the Developer option.
• Press OK on the window.

The Developer tab has been added to the Ribbon section.

Method 2 – Create a List Using ActiveX Controls

• Go to the Developer tab.
• Click the Insert option.
• Choose the List Box option from the ActiveX Controls.

• Move the cursor to any blank space of the dataset.

• Press the right button of the mouse.
• Choose Properties from the Context Menu.

• The Properties window will appear now.
• Modify the (Name) section to Sheet_List.
• Go to MultiSelect drop-down list and choose the 2 – fmMultiSelectExtended. Save the changes.

2 – fmMultiSelectExtended option offers to select multiple cells using the Ctrl or Shift buttons.

Method 3 – Customize the ActiveX Box

• Go to the dataset and move the cursor to the Control Box.
• Press the mouse’s right button again.
• Choose the View Code option from the Context Menu. Or press Alt+F11.

• 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.
• Change the Event to Active.

• Write 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.

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

Method 4 – Add Custom VBA Code to Print Sheet

• 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 used to resize a dynamic array.

    Sheets(array_1()).PrintOut

This command is used to print out a sheet.

Method 5 – Disable Design Mode

• To disable this, click on the Design Mode from the Developer tab.

Method 6 – Create a Control Button for Print

• Select the Insert option from the Developer tab.
• Choose a Button from the Form Controls option.

Method 6 – Assign the Macro to the Print Button

• Move the cursor to any blank space of the dataset. The Assign Macro will pop up. Click OK.

• A button was added to the dataset. Edit the button and change the name to Print Button.

• Click on any of the Sheets from the list and click on Print Button.

A pop up mentioning the printing command is showing here.

Choose sheets from the list and then press the Print Button to get your desired sheet to print.

Method 7 – Add an Icon to Print Specific Excel Sheet

• Choose the Illustration option from the Insert tab.
• Click on Icons and choose your desired icon.

• Place the data set icon and assign the macro as shown previously.

This Icon will work exactly like the Print Button. Choose the sheet from the list and click on this icon.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

Related Articles

• Create ListBox for Multiple Columns in Excel VBA
• How to Create Multi Select ListBox in Excel
• How to Populate Excel VBA ListBox Using RowSource
• How to Sort ListBox with VBA in Excel