Set up print button to function only on 1 sheet

[MartinBecks]

I have 4 sheets in my workbook. Only 1 sheet named "Data Sheet" do people enter information and depending on the numbers of rows filled out it prints accordingly. I'm ok there. My issue is in printing the other sheets which has instructions and reference material. When I try printing another tab the message box appears. How do I modify the code so the print button only works for the "Data Sheet"?

I know all I need is a little fix but I didn't originate this code, a colleague did. In advance thank you.


I have the following code in ThisWorkbook

Private Sub Workbook_BeforePrint(Cancel As Boolean)If PrtOK Then
Cancel = False
Else
MsgBox "Please use the embedded print button"
Cancel = True
End If
End Sub

and the following code in Module 1

Public PrtOK As BooleanSub Button41_Click()
PrtOK = True
Dim LastRow As Long
LastRow = Range("L" & Rows.Count).End(xlUp).Row
Set HeaderRange = Range("$A$1:$Q$16")
Set BodyRange = Range("$A$17:$Q$" & LastRow)
Set FooterRange = Range("$A$216:$Q$220")
Set UnionRange = Application.Union(HeaderRange, BodyRange)
With ActiveSheet.PageSetup
    .PrintArea = UnionRange.Address
End With
'show print dialog
Application.Dialogs(xlDialogPrint).Show
PrtOK = False
End Sub

 

[Michael M]

Maybe this

Public PrtOK As BooleanSub Button41_Click()
PrtOK = True
Dim LastRow As Long, ws As Worksheet
LastRow = Range("L" & Rows.Count).End(xlUp).Row
Set ws = Sheets("Data Sheet")
Set HeaderRange = ws.Range("$A$1:$Q$16")
Set BodyRange = ws.Range("$A$17:$Q$" & LastRow)
Set FooterRange = ws.Range("$A$216:$Q$220")
Set UnionRange = Application.Union(HeaderRange, BodyRange)
With ws.PageSetup
    .PrintArea = UnionRange.Address
End With
'show print dialog
Application.Dialogs(xlDialogPrint).Show
PrtOK = False
End Sub

 

[MartinBecks]

The coded didn't work. I copied and pasted your code into Module 1 and I still got the message box prompting me to use the print button.

 

[Michael M]

Private Sub Workbook_BeforePrint(Cancel As Boolean)
If ActiveSheet.Name <> "Data Sheet" Then Exit Sub
If PrtOK Then
Cancel = False
Else
MsgBox "Please use the embedded print button"
Cancel = True
End If
End Sub

 

[MartinBecks]

I copied and pasted your code to This Workbook and it works like a charm. Thanks alot Michael M.

 

[Michael M]

glad to help...