Print Row

N

nman

I am searching for code that I can attach to a checkbox
that will not allow a referenced row to be printed. Any
advice?
 
V

Vasant Nanavati

You would have to write code to hide the row in order for it not to be
printed. Also, how would you "reference" a row from a checkbox? You would
probably need to use a linked cell to identify the row that you want to
suppress.
 
G

Guest

New to this but I do understand your comments. Not sure
what the code should be to not print a row (or range).
 
N

nman

I need to attach the code (for each individual row) to
a "checkbox" so I can select the rows I don't want
printed. I do not want the row to disappear or "hide"
from the worksheet, just not print if the checkbox
contains a check.
 
V

Vasant Nanavati

As I stated in my initial response, you have to hide the row if you don't
want to print it. There is no other practical way.
 
N

nman

I tried the code for "Hide Rows or Cells when you print a
sheet" and it did not work for me.
 
V

Vasant Nanavati

Here's a possible solution:

1. Use checkboxes from the Forms Toolbar; not the Control Toolbox (the
former are more stable when used on worksheets). Ypu will probably want to
uncheck "Print Object" in the Format Control dialog.

2. Right-click on each one and set its LinkedCell property to a cell in the
row that the checkbox relates to (say in column Z, if that's far enough
out). You can even hide the column as it serves no purpose other than to
carry the value of the checkboxes.

3. Put the following code in the ThisWorkbook code module:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim c As Range
With ActiveSheet
For Each c In Intersect(.UsedRange, .Range("Z:Z"))
If c = True Then c.EntireRow.Hidden = True
Next
Application.EnableEvents = False
On Error GoTo ErrorHandler
.PrintOut
Application.EnableEvents = True
End With
ErrorHandler:
MsgBox "Error " & Err.Number & vbNewLine & Err.Description, _
vbOKOnly + vbCritical, "Error"
ActiveSheet.UsedRange.EntireRow.Hidden = False
Application.EnableEvents = True
End Sub

It's really pretty messy but hopefully will put you on the right track.

--

Vasant
 
D

Dave Peterson

It might be to use Data|filter|Autofilter.

Apply the filter to a column and show only the rows you want to print.

I'd drop the checkboxes and use a simple X in that column of cells.
(Filter by Blanks or non-blanks)

But you could make each checkbox link to a cell in that row.
(Then you'd filter by True/False)

(if you exclude that column from the print range, it'll look ok.)

A little extra work, but I think it's easier.
 
Top