ExcelBanter - Page Setup protection

I don't think that there's anything that can make it bullet proof. If you try
to use a macro to make sure that the print range is what you wanted, then macros
could be disabled (or events could be disabled).

But this kind of thing would work for to stop most people (I think).

Put this macro in a General module:

Option Explicit
Sub printme()
Dim myAddr As String
Select Case LCase(ActiveSheet.Name)
Case Is = lcase("sheet1"): myAddr = "A1:c9"
Case Is = lcase("sheet2"): myAddr = "C1:c3"
Case Is = lcase("sheet3"): myAddr = "a1:d6"
Case Else
myAddr = ""
End Select

If myAddr = "" Then
'do nothing special
MsgBox "This sheet cannot be printed!"
Else
Application.EnableEvents = False
ActiveSheet.Range(myAddr).PrintOut
Application.EnableEvents = True
End If

End Sub

Then put this under the ThisWorkbook module:

Option Explicit
Private Sub Workbook_BeforePrint(Cancel As Boolean)
Cancel = True
MsgBox "Please use the buttons to print the sheet!"
End Sub

Now, place a button from the Forms toolbar on each worksheet and assign the
"printme" macro to each of the buttons.

If macros are disabled or events are disabled, this breaks!

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

=======
As an afterthought...

This might be better if you want to allow the user to preview their data:

Option Explicit
Sub printme()
Dim myAddr As String
Dim resp As Long

Select Case LCase(ActiveSheet.Name)
Case Is = "sheet1": myAddr = "A1:c9"
Case Is = "sheet2": myAddr = "C1:c3"
Case Is = "sheet3": myAddr = "a1:d6"
Case Else
myAddr = ""
End Select

If myAddr = "" Then
'do nothing special
MsgBox "This sheet cannot be printed!"
Else
resp = MsgBox(Prompt:="Click Yes for Print, No for Print Preview!", _
Buttons:=vbYesNo, Title:="Print or Print|Preview")
Application.EnableEvents = False
ActiveSheet.Range(myAddr).PrintOut preview:=CBool(resp = vbNo)
Application.EnableEvents = True
End If

End Sub

Wayne wrote:

How can I keep someone out of the "File Print" area on a protected
workbook/worksheet? The only cells accessible are the ones that I want used
for data entry, and I've set the print range to the cells that I want
printed, but it all falls apart when I (or some nefarious soul) wander into
the "File / Print" area. I can clear the print range, set a new print range,
add headers and footers...generally make a mess of things. How can I keep
people locked out of this area and force them to use the Print Icon to print
only the range that I've pre-selected? I've protected the Sheet AND the
Workbook, but it doesn't look too protected to me. It has to be bulletproof.

--

Dave Peterson