Give users three print options with VBA and macro buttons

 

byKara Hiltz

Application: Microsoft Excel 2007/2010/2013/2016

Operating System: Microsoft Windows

Download: http://download.elijournals.com/excel/201808/PrintVBA.zip

 

Sometimes you need to design a worksheet that is foolproof even for those who aren’t all that familiar with Excel. With help from VBA, you can create buttons in your worksheet that provide easy printing options for anyone who opens the workbook.

 

To add one or more custom print buttons to our worksheet using VBA, we’ll:

  • Write the three macros that will allow us to print: an entire worksheet, a data tableonly, or a chart object only.
  • Create three distinguishable buttons in our worksheet and assign a macro to each of the print buttons.
  • Test our print buttons to make sure that they behave the way we expect.

 

Macros and VBA intimidate a lot of Excel users—or may be completely off the radar for others. While not always easy to deal with, VBA can help you get more out of your Excel workbooks. One of the best parts about using VBA is that you can make it easier for other Excel users to get tasks done.

To demonstrate, we’ll show you how to add buttons that print a specific portion of your worksheet (or the entire worksheet) with just one click, as shown in Figure A. The VBA code behind these buttons is relatively simple, but the buttons can save Excel users a lot of hassle.

 

IME18802A

A:

These three buttons give Excel users the option to print the whole worksheet, the data table only, or the chart only.

 

Download: To follow along with our example, just download and extract the file Sales by Rep.xlsm from the URL given at the beginning of the article.

 

Explore the PrintOut method

Before you begin creating buttons for printing, you’ll want to know the options you have. In our example, we have an Excel table in our worksheet that allows us to easily filter data. We also have a chart that updates automatically based on the data selected in the data table, as shown in Figure A.

For each of our three macros, we will execute the PrintOut method. While the method will stay consistent for all three buttons, the object will change. We will use the PrintOut method for the ActiveSheetobject, the Chart object, and the Selection object. The PrintOut method allows you to customize how you will print an object without using the Print dialog box. Here’s the syntax for PrintOut:

 

.PrintOut(From, To, Copies, Preview, ActivePrinter, PrintToFile, Collate, PrToFileName, IgnorePrintAreas)

 

For our example, we won’t specify any of the available properties listed, but you can modify the code for your own purposes if you’d like to customize the print job. Check out Table A for an explanation of all the available PrintOut properties.

 

Table A:

Properties of the PrintOut method

Name

Description

Example

From

The page number where printing starts. If omitted, printing starts at page 1.

.PrintOut From:=3, To:=4

To

The page number where printing ends. If omitted, printing ends at the last page.

.PrintOut From:=3, To:=4

Copies

Specifies the number of copies to print

.PrintOutCopies:=2

Preview

True/False. Displays a print preview before printing. If omitted/false, it prints directly.

.PrintOutPreview:=True

ActivePrinter

Sets the name of the printer to which it will print.

.PrintOutActivePrinter:=”PrinterName”

PrintToFile

True/False. If true, prints to a file instead of paper. If omitted/false, it prints to paper.

.PrintOutPrintToFile:=True

Collate

True/False. If true, collates multiple pages. If omitted/false, does not collate.

.PrintOutCollate:=True

 
Join NowClose