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

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

Alok

Alok

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.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo