Create a Print button – macro

This article describes how to create a button and place it on an Excel worksheet, then assign a macro to that button allowing the user to print the worksheet when the button is pressed on.

1. Create a Print button on a worksheet - macro

A print button located on a worksheet is a great feature that allows the user to quickly print a worksheet. You need a form control button and a macro to build a print button on a worksheet.

What is the biggest downside with a macro assigned to a button?
Most companies have macros disabled because of security reasons.

What is a macro?
A subroutine that contains lines of code made of VBA which is an abbreviation of visual basic of application.

Is it complicated to set up?
No, it is easy and you will learn something new that will be interesting.

1.1 Create a button and place it on the worksheet

The button is found in the Form Controls group, here are the steps:

1. Press with left mouse button on "Developer tab" on the ribbon.
   Developer tab missing?
2. Press with left mouse button on the "Insert" button. A popup menu appears containing multiple controls.
3. Press the "Button (Form Control)" shown on the image above next to the blue arrow.
4. Press and hold with mouse button on the worksheet where you want it located.
5. Drag with mouse while holding the mouse button to adjust the button size.
6. Release the mouse button.
7. A dialog box shows up telling you to assign a macro. Press with left mouse button on "Cancel", we need to assign the macro after putting the code in a module explained below.

Change the button name to Print.

1.2 Create macro

You need the following code to be able to assign the macro to the button.

Sub Macro1()
  Application.Dialogs(xlDialogPrint).Show
End Sub

1.3 Where to put the code?

(The macro shown in the image above is not used here in this article, it only demonstrates where to paste the code.)

1. Press Alt-F11 to open the visual basic editor
2. Press with mouse on "Module" on the "Insert" menu, see the image above.
3. Copy and paste "Sub Macro1" code below to code module.
4. Go back to Excel.

1.4 Assign the macro the button

1. Press with right mouse button on with the mouse on the button. A popup menu appears.
2. Press on "Assign Macro..". A dialog box appears containing available macros to assign.
3. Select the macro you want to assign, in this case Macro1.
4. Press the "OK" button.

Save the Excel file as a *.xlsm in order to keep the macro attached to the workbook. The reason is that a *.xlsx file can't contain macros.

1.5 Watch a video where I explain the steps to insert a print button

Get the Excel file

Print-invoice.xls

2. Create a Print button in a cell

This section describes how to align and resize a print button to a specific cell, the image above shows a print button with the same size as cells H6 and I6.

The instructions here are for those that have already created a print button. If you have not go to section 1 above and create one first.

How to select a form control without running the assigned macro?

1. Press and hold the CTRL button on the keyboard.
2. Select the format control "button".
3. Release the CTRL key.

How to align the button border to a cell border?

1. Make sure the button is already selected, you can tell it is selected if the "handles" around the format control button are shown.
2. Press and hold the "Alt" key.
3. Press and hold with mouse button on one of the "handles".
4. Drag with mouse to the cell border you want to align with. It snaps automatically in place if you get close enough.
5. Repeat with the remaining "handles".

Go back to section 1.2 above and follow the instructions there to complete the setup.

3. Create a Print button on a worksheet without macro

To my knowledge it is not possible to run the "Print" command by pressing a button located on a worksheet without using a macro. However, you have a few other options:

• Create instructions on the worksheet to use the shortcut keys CTRL + P to quickly print a spreadsheet.
• You can easily put a print button on the Quick Access Toolbar.
• Enable a print button on the ribbon.

I will now show you how all three examples above are created.

3.1 Use a text box to inform the user about the shortcut keys CTRL + P

The image above demonstrates a basic text box containing instructions on how to use the shortcut keys to command a print. A text box requires no macro or VBA code. Here is how to create it:

1. Go to tab "Insert" on the ribbon.
2. Press on "Text box" button.
3. Press and hold with mouse button on the worksheet where you want it located. Don't worry, you can move it later on if a mistake is done.
4. Drag with mouse to resize the text box.
5. Release the mouse button.

Press with the mouse on the "text box" to select it.
Press with the mouse on the "text box" text to show the prompt. You can now change the text inside the "text box".
The handles around the "text box" allow you to resize the "text box".
A new tab on the ribbon is shown when the "text box" is selected, it is named "Shape Format". It has settings that allow you to change the background color, the shape outline and so on.

3.2 Print button on the Quick Access toolbar

The image above shows how to create a print button on the Quick Access Toolbar. The downside with this approach is that it is only a customization to your Excel application only, it is not transferred to other users if you send them a copy of your workbook.

1. Press with mouse on the "arrow" button on the top menu bar. A popup menu appears.
   
2. Enable the "Quick Print" button or the "Print Preview and Print". The "Quick Print" button prints the active worksheet without any preview at all.
   

The "Print" button appears on the Quick Access Toolbar.

The "Print Preview and Print" button lets you preview the print before sending it to the printer.

3.3 Enable a print button on the ribbon

The downside with this approach as well is that it is only a customization to your Excel application only. It is not transferred to other users if you send them a copy of your workbook.

1. Press with right mouse button on an empty space on the ribbon. A popup menu appears.
   
2. Press with mouse on "Customize the Ribbon...", a dialog box appears.
   
3. Create a new group in the "Home" tab, see the image above.
4. Rename the group if you like.
   
5. Go to "Choose commands from:" and select the File Tab. See the image above step 1.
6. Scroll down to "Print Preview and Print" and select it.
7. Press the "Add >>" button to move it to the new custom group you just created.
8. Press the OK button to apply changes.

The "Print Preview and Print" button appears on the ribbon.

4. Create a Print button - multiple sheets

The following vba macro is assigned to a print button shown in the image above. It selects the specified worksheets and then shows the print dialog box.

Section 1 above demonstrates how to:

1. Create a button on the worksheet.
2. Where to put the vba macro.
3. How to assign the macro to a print button.
4. Save the workbook as a *.xlsm file to keep the macro attached.

I recommend you follow those steps. Only the macro below is needed.

Sub Print_multiple_ws()

'Dimesnion variables and declare data types
Dim ws_list As Variant

'Specify worksheet names and save array to variable
ws_list = Array("Invoice", "Data")
 
'Select worksheets
Worksheets(ws_list).Select

'Show print dialog box
Application.Dialogs(xlDialogPrint).Show
   
   
End Sub

5. Create a Print button - pdf

This example demonstrates a button on the worksheet that asks for the file name of the PDF to be saved, then saves the PDF and opens it using the default PDF software.

Here is what happens when you press the "Print to PDF" button:

1. A dialog box appears asking for the file name.
2. A PDF is generated, saved, and opened with the default PDF software.

Here is how to build the Print to PDF button:

5.1 Copy vba code

Copy the VBA code below.

 

Sub Print_pdf()

'Dimesnion variables and declare data types
Dim ws_name As Variant

'Ask for the file name
ws_name = InputBox("PDF file name?")

'Check if file name is not empty
If StrPtr(ws_name) <> 0 Then
    
    'Export the worksheet to a PDF
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
                                    Filename:=ws_name, _
                                    Quality:=xlQualityStandard, _
                                    IncludeDocProperties:=False, _
                                    IgnorePrintAreas:=False, _
                                    OpenAfterPublish:=True
                                
End If

End Sub

 

5.2 Put the vba code in a module

(The vba code shown in the image above is not used here in this example, it only demonstrates where to paste the code.)

1. Press Alt-F11 to open the visual basic editor.
2. Press with left mouse button on Module on the Insert menu.
3. Copy and paste "Sub Print_pdf" code above to code module.
4. Go back to Excel.

5.3 Create the worksheet button

The button is found in the Form Controls group, here are the steps:

1. 1. Press with left mouse button on "Developer tab" on the ribbon.
      Developer tab missing?
   2. Press with left mouse button on the "Insert" button. A popup menu appears containing multiple controls.
   3. Press the "Button (Form Control)" shown on the image above next to the blue arrow.

1. Press and hold with mouse button on the worksheet where you want it located.
2. Drag with mouse while holding the mouse button to adjust the button size.
3. Release the mouse button.
4. A dialog box shows up telling you to assign a macro. Press with left mouse button on "Cancel", we need to assign the macro after putting the code in a module explained below.

Change the button name to "Print to PDF".

6. Create a Print button - selection

This VBA macro is assigned to a print button shown in the image above. It lets you specify a cell range you want to print and then shows the print dialog box.

Section 1 above demonstrates how to:

1. Create a button on the worksheet.
2. Where to put the vba macro.
3. How to assign the macro to a print button.
4. Save the workbook as a *.xlsm file to keep the macro attached.

I recommend you to follow these steps. Only the macro below is needed.

 
Sub Print_selection()

'Dimesnion variables and declare data types
Dim rng As Range

'Define cell range to print
Set rng = Application.InputBox("Select a cell range to print: ", , , , , , , 8)

'Select worksheets
rng.Select

'Show print preview
rng.PrintOut Preview:=True
   
   
End Sub

Form controls category

Working with COMBO BOXES [Form Controls]
This blog post demonstrates how to create, populate and change comboboxes (form control) programmatically. Form controls are not as flexible […]

Working with LIST BOXES (Form Controls)
This blog post shows you how to manipulate List Boxes (form controls) manually and with VBA code. The list box […]

Working with TEXT BOXES [Form Controls]
There are two different kinds of text boxes, Form controls and ActiveX Controls. Form controls can only be used on […]

Macro category

How to create an interactive Excel chart
Table of Contents How to create an interactive Excel chart How to filter chart data How to build an interactive […]

Excel calendar
Table of Contents Excel monthly calendar - VBA  Calendar Drop down lists Headers Calculating dates (formula) Conditional formatting Today Dates […]

Save invoice data – VBA
Table of contents Save invoice data - VBA Invoice template with dependent drop down lists Select and view invoice - […]

Print category

Remove print preview lines (Page Breaks)
Have you ever wondered how these lines got there on a worksheet? They are called "print preview lines" and they […]

Print screen the entire worksheet
This article demonstrates different techniques on how to take a screenshot of your worksheet. You can do that by simply […]

Print consecutive page numbers across multiple worksheets
This article describes how to print page numbers in consecutive order through specified worksheets and how to repeat column headers […]

Excel categories

Comments (19)