How do I lock the print in Excel?

Question

Last month I talked about how you can freeze rows or columns in an Excel workbook so that your headings stay visible on screen. This month I’ll show you how you can choose rows or columns to appear on every page when you print. This lets you print a report heading and/or column/row headings on each page without having to actually place them throughout your worksheet. This is all you have to do:

Table of Contents Show

The Best Office Productivity Tools
Can you lock print settings in Excel?
How do I permanently set print area in Excel?

Click the Page Layout tab on the ribbon.
Click the Print Titles button in the Page Setup section of the ribbon.
To get rows from your spreadsheet to appear at the top of every printed page, click in the Rows to repeat at top box. Then, with the Page Setup window still open, click on the row of your choice. You can select multiple rows by clicking on the first row and dragging to select a range of rows.
To get one or more columns to print on the left side of each page, click in the Columns to repeat at left box, then click on the column or columns you want to have printed on each page.

Here are a couple of additional details. The rows (or columns) to repeat have to be adjacent, for example you can’t select rows 1 and 3, but not 2. Also, you only select entire rows (or columns) to repeat.

The rows you selected don’t have to be at the top of your worksheet – i.e. row 1. Let’s say you have a report heading in rows 1 through 3 and column headings in rows 4 and 5. If you select rows 4 and 5 as “Rows to repeat at top” page 1 will still print as it appears on screen but beginning with page 2, only rows 4 and 5 (not 1-3) will appear at the top of each printed page.

To take that example to the extreme, if you selected row 500 at “Rows to repeat at top” it would not begin repeating until the page after the page that row 500 naturally prints on.

Karolyne shares workbooks with other people. Once in a while those people will, without knowing it, make changes to a worksheet that results in many, many pages being printed. Karolyne is looking for a way to set a print area in such a way that it is "locked" and could not be changed or removed.

There are a couple of things you can try. First, you can set your print area and then apply worksheet protection that allows only some of the cells in the worksheet to be selected. This will preclude those strange changes that result in huge printouts. It won't, however, stop someone from changing the print area so it includes only those unprotected cells.

The only way to "protect" the print area is to use a macro that will force the desired print area. One natural place to enforce this is just before printing. The following event handler (added to the ThisWorkbook module) will change the print area for worksheet Sheet1 to the range A1:C25:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
    Worksheets("Sheet1").PageSetup.PrintArea = "A1:C25"
End Sub

This approach will only work, obviously, if the user enables macros when the workbook is opened. You can change the specified sheet name and range as desired.

In our daily work, there may be some important information in the worksheet, in order to protect the internal data outspread, the department will not allow us to print them. And here, I will talk about how to prevent users from printing worksheet.

Prevent users from printing one specified worksheet with VBA

Prevent users from printing the whole workbook with VBA

Office Tab Enable Tabbed Editing and Browsing in Office, and Make Your Work Much Easier...

Prevent users from printing one specified worksheet with VBA

Amazing! Using Efficient Tabs in Excel Like Chrome, Firefox and Safari!Save 50% of your time, and reduce thousands of mouse clicks for you every day!

You can apply the following VBA code to protect your specific worksheet to be printed.

1. Activate your worksheet that you want to disable its print feature.

2. Hold down the ALT + F11 keys to open the Microsoft Visual Basic for Applications window.

3. And then in the left Project Explorer, double click ThisWorkbook to open the module, and paste the following code into it:

VBA code: prevent users from printing one specified worksheet

Private Sub Workbook_BeforePrint(Cancel As Boolean)
'Update 20140625
Dim WsName As String
WsName = "Sheet1"
For Each xWs In Application.ActiveWorkbook.Windows(1).SelectedSheets
    If xWs.Name = WsName Then
    MsgBox ("You can not print this worksheet")
        Cancel = True
    End If
Next
End Sub

Note: In the above code, the Sheet1 is the active worksheet which you are not allowed to print.

4. Then save and close this code, and now when you try to print this specific worksheet, you will get the following warning.

Note: With this code, the only specific worksheet is not allowed to print, but other sheets can be printed as usual.

Prevent users from printing the whole workbook with VBA

If you need to prevent users from printing the whole workbook, here also have a code can do you a favor.

1. Open your workbook which you don’t allow others to print.

2. Hold down the ALT + F11 keys to open the Microsoft Visual Basic for Applications window.

3. And then in the left Project Explorer, double click ThisWorkbook to open the module, and paste the following code into it:

VBA code: prevent users from printing one specified worksheet

Private Sub Workbook_BeforePrint(Cancel As Boolean)
'Update 20140626
Cancel = True
MsgBox "You can't print this workbook"
End Sub

4. Then save and close this code, and go to back the workbook, and now when you print one worksheet or the entire workbook, they will not allowed to be printed and you will get the following warning:

The Best Office Productivity Tools

Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
More than 300 powerful features. Supports Office / Excel 2007-2021 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.

Read More... Free Download... Purchase...

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
Open and create multiple documents in new tabs of the same window, rather than in new windows.
Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!

Read More... Free Download... Purchase...

Oldest First

Sort comments by

Oldest First

Newest First

Comments (5)

No ratings yet. Be the first to rate!

Greg Burrell

about 5 years ago

#18203

This comment was minimized by the moderator on the site

This works in theory, but by default Excel disables all macros when you open a workbook, which prevents this code from executing and allows printing as usual.

Reply

0

Mike Greg Burrell

about 5 years ago

#18204

This comment was minimized by the moderator on the site

This works but when you email the file, the user on that end can print it... which defeats the purpose...

Can you lock print settings in Excel?

There are a few different ways to protect print settings in Excel. One way is to use the Print Area feature. This allows you to specify which cells you want to print, and then you can protect the print area so that it can't be changed. Another way to protect print settings is to use the Page Break Preview feature.

How do I permanently set print area in Excel?

Set one or more print areas.

On the worksheet, select the cells that you want to define as the print area. Tip: To set multiple print areas, hold down the Ctrl key and click the areas you want to print. ... .

On the Page Layout tab, in the Page Setup group, click Print Area, and then click Set Print Area..

Cara Belajar How Print column Excel