Home » Macros & Excel VBA » Excel Button to Print Specific Sheets (With Easy Steps)

Excel Button to Print Specific Sheets (With Easy Steps)

Alok Paul Alok Paul Feb 12, 2023 8
Get FREE Advanced Excel Exercises with Solutions!

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.

Table of Contents hide
Download Practice Workbook
How to Create an Excel Button to Print Specific Sheets
Steps to Create a Button to Print Particular Excel Sheets
Step-1: Add Developer Tab in Excel
Step-2: Create a List Using ActiveX Controls
Step-3: Customize the ActiveX Box
Step-4: Add Custom VBA Code to Print Sheet
Step-5: Disable Design Mode
Step-6: Create a Control Button for Print
Final Step: Assign the Macro to the Print Button
Add an Icon to Print Specific Excel Sheet
Conclusion
Related Articles

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.

Create a Button to Print Particular Excel Sheets

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.

Create a Button to Print Particular Excel Sheets

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

Create a Button to Print Particular Excel Sheets

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.

Create a Button to Print Particular Excel Sheets

  • There is SelectionChange on the default Event on the right side of the VBA window.
  • But we change the Event to Active.

Create a Button to Print Particular Excel Sheets

  • 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

Create a Button to Print Particular Excel Sheets

  • 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:

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

Create a Button to Print Particular Excel Sheets

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.

Create a Button to Print Particular Excel Sheets

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.

Create a Button to Print Particular Excel Sheets

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.

Create a Button to Print Particular Excel Sheets

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.

Related Articles

Tags:

Alok Paul
Alok Paul

Hello, this is Alok. I am working as an Excel & VBA Content Developer at Exceldemy. I want to provide solutions to various Excel-based problems. I completed my study at East West University major in Telecommunications Engineering. I love traveling, reading books, playing cricket.

8 Comments
  1. Reply
    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?

  2. Reply
    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 Avatar photo
      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

  3. Reply
    Mark Jun 14, 2023 at 7:01 PM

    Thank you for the code it works as indicated. How can I print multiple sheets (30)?

  4. Reply
    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 Avatar photo
      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

Leave a reply

About ExcelDemy.com

ExcelDemy is a place where you can learn Excel, and get solutions to your Excel & Excel VBA-related problems, Data Analysis with Excel, etc. We provide tips, how to guide, provide online training, and also provide Excel solutions to your business problems.

Contact | Privacy Policy | TOS

Affiliate Disclosure:

ExcelDemy.com is a participant in the Amazon Services LLC Associates Program, an affiliate advertising program. I earn a small commission if you buy any products using my affiliate links to Amazon.

DMCA.com Protection Status

Join ExcelDemy Forum

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo