Locking out printing

C

Col

Hi all,

Is it at all possible to lock out printing on a sheet until a particular
cell is entered/check box clicked.

I know you can remove certain buttons/toolbars but someone could still press
ctrl-p to print the sheet, I'm looking to lock it out entirely.

Many thanks for any help.

Colin.
 
D

Dave Peterson

How about stopping printing anything in that workbook until that cell is filled
in?

Put this in the ThisWorkbook module:

Option Explicit
Private Sub Workbook_BeforePrint(Cancel As Boolean)
If IsEmpty(Me.Worksheets("Sheet1").Range("a1")) Then
MsgBox "Please fill in Sheet1 Cell A1"
Cancel = True
End If
End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

You can read more about these kinds of events at:
Chip Pearson's site:
http://www.cpearson.com/excel/events.htm

David McRitchie's site:
http://www.mvps.org/dmcritchie/excel/event.htm
 
C

Col

That's great, worked a treat.

My only question for now is, can you lock a specific sheet within a
workbook?

The code you gave me is fine but locks out all the workbook?

Thank you,

Colin.
 
D

Dave Peterson

I don't think so.

You can group sheets, then print multiple sheets.
You can do File|print|and chose entire workbook.

I don't know a way of getting "inside" those options to find out what sheets
you're printing.

This might work if you only print the activesheet:

Option Explicit
Private Sub Workbook_BeforePrint(Cancel As Boolean)
If LCase(ActiveSheet.Name) = "sheet1" Then
If IsEmpty(Me.Worksheets("Sheet1").Range("a1")) Then
MsgBox "Please fill in Sheet1 Cell A1"
Cancel = True
End If
End If
End Sub


But it's not too difficult to see how a user could "cheat" this.

=======
Another option is to put a cell in the print range (near the header).

=if(A1<>"","","This workbook is not complete--don't trust it!")

Or some warning that scares the heck out of anyone reading the paper copy.
That's great, worked a treat.

My only question for now is, can you lock a specific sheet within a
workbook?

The code you gave me is fine but locks out all the workbook?

Thank you,

Colin.
Click to expand...
 
C

Col

Never mind, I can use the code you supplied on separate files, was just
being lazy trying to do it all in one workbook:)

Thanks again for the help - wonderful.

Colin.
 
Top