View but not print out

[Aussie-Clive]

G'day All,

Does anyone know if there's a way to either define what columns or rows are printable or to somehow leave columns (or rows) as visible within the print area but omitted from printing as if they were hidden.

In my CAD package I can simply elect to have certain collections of information "non-printable", is there any way I can do this with columns without having to rely on people managing to laboriously (sic) hide and unhide columns either side of printing a document.

Some of the information is too sensitive to risk being inadvertantly sent out.(We've all heard of it happening)

Cheers

Clive

[dominicb]

Good afternoon Aussie-Clive

Generally, Excel will print whatever is set within the print range. The simplest way would be to have a macro that (could be easily recorded by the macro recorder) would hide any rows / columns and then sent to the printer. That would sort out the laborious bit. You could also have a piece of code that is fired by the print event, so when someone attempts to send something to the printer, several rows / columns hidden status is checked and if they aren't hidden then a confirm message is flashed up and the operation is aborted. Would something like this be acceptable?

You could also lump these two pieces of code together, but that would remove an element of control, making it tricky if you actually wanted to print out these rows / columns on occasion.

HTH

DominicB

[Aussie-Clive]

Thanks for the ideas Dominic,

What you suggest is certainly acceptable, though it's a pity there isn't a more straightforward solution.

Though I'm happy to tackle coding - I certainly couldn't be credited with any competence!

How would I go about structuring this and in what form is the print event referenced?

Cheers

Clive

[zbor]

You can set print area outside parameters you see.

For example your data is from column A to column D but you want to print C and D -> just set prin area at that part of sheet.

[dominicb]

Hi Aussie-Clive

This would be a starting point. It needs to go into the ThisWorkbook module of the VBE (press Alt + F11 to open the VBE). It's a simple check that will intercept the printer call and make the decision whether or not to print : if column "D" and row "10" are visible, then the printer call is aborted, otherwise the document will be sent to the printer :

Private Sub Workbook_BeforePrint(Cancel As Boolean)
If Columns("D:D").EntireColumn.Hidden = False Or _
   Rows("10:10").EntireRow.Hidden = False Then
MsgBox "Rows are not hidden"
Cancel = True     'This statement cancels the print command
Exit Sub
End If
End Sub

As I say, it's a starting point and can be refined further according to any suggestions you might want to make.

HTH

DominicB

[Aussie-Clive]

My brain seems to be in Sunday-mode, I can't seem to get that to work.

Can you explain for the hard of thinking how I do this after the Alt-F11 as I've clearly got something wrong!

Not enough Chorley cakes perhaps?

Cheers

[dominicb]

Hi Aussie-Clive

Can you explain for the hard of thinking how I do this after the Alt-F11 as I've clearly got something wrong!

From Excel, plress Alt + F11. This will open the VBE.
From the Project - VBA Project window on the left find your file.
Double click on the This Workbook object. This will open a new pane.
Paste your code into this pane.
Go to File > Close and Return to Microsoft Excel.

HTH

DominicB

[Aussie-Clive]

Brilliant, Thanks All.

Sorry Dominic, I didn't have the project pane visible and couldn't figure it out as this isn't my area. appreciate your patience. It all works well and I'm sure I can figure it out from here. I also like Martins lateral thinking.

I still feel that this is an area that MS could possibly improve on, by having a print / non print definition built in - as I say we all know of someone who has tripped over their own shoelaces printing confidential information in error, so it's not like it's an uncommon problem.

I'll mark it as solved and thanks again.

Cheers from Oz.

[martindwilson]

just another suggestion if its always the same columns you want to hide
make another sheet called print
reference all the columns you want on it set print area
and only use that sheet for printing