Excel Button to Print Specific Sheets (With Easy Steps)

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

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, and then print them. Here, we will discuss each step of the 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 added the Developer tab by customizing the ribbon section.

Go to File > Options.
Select the 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.

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

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.

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

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.

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

Download Practice Workbook

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

Print Sheets with a Button.xlsm

Conclusion

In this article, we described the total process of creating an Excel Button to print specific sheets step by step in detail. I hope this will satisfy your needs. Please give your suggestions in the comment box.

Get FREE Advanced Excel Exercises with Solutions!

9 Comments

Gabor Aug 18, 2022 at 5:21 PM

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?
- Reply
  Masum Mahdy Sep 29, 2022 at 2:59 PM
  
  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.
Sam Feb 6, 2023 at 11:45 PM

Hi!
Anyway to create a locked list, instead of generating one? Every time I open the workbook, my list is empty.
- Reply
  Eshrak Kader Feb 19, 2023 at 3:10 PM
  
  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
Mark Jun 14, 2023 at 7:01 PM

Thank you for the code it works as indicated. How can I print multiple sheets (30)?
- Reply
  Rafiul Hasan Jun 15, 2023 at 11:10 AM
  
  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
Caner Sep 5, 2023 at 12:38 PM

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?
- Reply
  Aniruddah Alam Sep 7, 2023 at 6:00 PM
  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:
```
Private Sub Worksheet_Activate()
    Dim Sheet_Name As Worksheet
    Me.Sheet_List.Clear
    For Each Sheet_Name In ThisWorkbook.Sheets
        'only taking visible sheet in the list
        If Sheet_Name.Visible = xlSheetVisible Then
            Me.Sheet_List.AddItem Sheet_Name.Name
        End If
    Next Sheet_Name
End Sub
```
  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:
```
Sub print_specific_sheets()
Dim n As Long, m As Long
Dim array_1() As Variant
m = 0
    With ActiveSheet.Sheet_List
        If .ListCount = 0 Then
            MsgBox "No Sheet Selected"
            Exit Sub
        End If
        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
        'Opening Printer Setup Dialogue box
        Application.Dialogs(xlDialogPrinterSetup).Show
        Sheets(array_1()).PrintOut
    End With
End Sub
```
  Here, I only added the following line to show the Printer Setup dialogue box from where you can choose from available printers.
```
Application.Dialogs(xlDialogPrinterSetup).Show
```
  I hope it solves your problem.
  
  Regards
  Aniruddah
  Team Exceldemy
deven Oct 25, 2023 at 10:59 PM

few if any I encountered are willing to help. This one is an exception !!