Locking out printing

  • Thread starter Thread starter Col
  • Start date Start date
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.
 
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
 
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.
 
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...
 
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.
 
Back
Top