Re-Post: Before_Print Sub Doesn't Trigger

[Tom Ogilvy]

You can declare it, or you can remove it

remove the line
vVal = wsSheet.PageSetup.Zoom





--
Regards,
Tom Ogilvy

Tom,

I copied this latest into the ThisWorkbook module (Option Explicit is set)

and received a compile error: Variable not defined. Highlighted is: vVal =

 

[Dave Peterson]

I think Tom's code is what you want. So don't do those changes.

(when I read your original code, I thought you wanted to print out each
cell--something weird, but I figured that you knew what you wanted. Tom
realized that that would be nuts (technical term) and modified your code to
print out each Area in your range.)

But just as closure:

"Option Compare Text" can go directly below "Option Explicit"

you'd keep the line, but remove the word lcase (and maybe the extra
parentheses).

 

[Phil Hageman]

Dave, Thanks very much for your advice through all this - I appreciate it.

 

[Phil Hageman]

Tom, I removed the line and the code works! Thank you very much

When the message box comes up asking for OK to print a particular range, can we add a "no" option to not print that range? Sometimes the second or third range may be empty of data

Thanks
Phil

 

[Dave Peterson]

If you just want to stop the printing of ranges with nothing in them, this could
be at the bottom of your code:

For Each ar In rng.Areas
MsgBox "Zoom: " & wsSheet.PageSetup.Zoom _
& " - " & ar.Address(external:=True)
If Application.CountA(ar) > 0 Then
ar.PrintOut
End If
Next

application.counta() just counts the cells that have something in them.

But it will include formulas that evaluate to "".

 

[Tom Ogilvy]

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim wsSheet As Worksheet
Dim rng As Range, ar As Range
Dim lngZ As Long
Dim ans as Variant
' MsgBox "In BeforePrint"
For Each wsSheet In ActiveWindow.SelectedSheets
Select Case LCase(wsSheet.Name)
Case "scorecard"
lngZ = 95
With wsSheet
Set rng = .Range("B1:BA45")
End With
Case "customer", "financial", "learning", "process"
lngZ = 90
With wsSheet
Set rng = .Range("B1:BA32,B33:BA64,B65:BA96")
End With
Case Else
With wsSheet.PageSetup
.FitToPagesWide = 1
.FitToPagesTall = 1
End With
Exit Sub
End Select
vVal = wsSheet.PageSetup.Zoom
With wsSheet.PageSetup
.Zoom = lngZ
End With
Cancel = True
On Error GoTo ErrHandler
Application.EnableEvents = False
For Each ar In rng.Areas
ans = _
MsgBox( "Zoom: " & wsSheet.PageSetup.Zoom _
& " - " & ar.Address(external:=True) & _
vbNewLine & vbNewline & "Print this out?", vbYesNo)
if ans = vbYes then
ar.PrintOut
end if
Next
Next
ErrHandler:
Application.EnableEvents = True
End Sub

--
Regards,
Tom Ogilvy

Tom, I removed the line and the code works! Thank you very much.

When the message box comes up asking for OK to print a particular range,

can we add a "no" option to not print that range? Sometimes the second or
third range may be empty of data.

 

[Phil Hageman]

Tom

This works great. Thanks. A final, I think, refinement. In the message box, can we substitute the file name with Page numbers and number of copies

For example, the message box would say: Print Page 1? Yes No (We can omit reference to the zoom size)
according to the following schedule

On "scorecard", Page 1 (the only page) would be for range B1:BA4

On "financial", "learning", and "process", Page 1 would be for range B1:BA32; Page 2 would be for B33:BA64
and Page 3 would be for B65:BA96. Would there be a way to ask for all three print pages in one message box, with th
number of copies

Example: Print Page 1 ? Yes No Number of Copies: __
Print Page 2 ? Yes No Number of Copies: __
Print Page 3 ? Yes No Number of Copies: __
Thanks
Phil

 

[Tom Ogilvy]

You would need to put up a userform to solicit multiple values from the
user. A message box only offers buttons to click. an Input box allows entry
of a single answer.

--
Regards,
Tom Ogilvy

Tom,

This works great. Thanks. A final, I think, refinement. In the message

box, can we substitute the file name with Page numbers and number of copies?

For example, the message box would say: Print Page 1? Yes No (We

can omit reference to the zoom size),

according to the following schedule:

On "scorecard", Page 1 (the only page) would be for range B1:BA45

On "financial", "learning", and "process", Page 1 would be for range

B1:BA32; Page 2 would be for B33:BA64;

and Page 3 would be for B65:BA96. Would there be a way to ask for all

three print pages in one message box, with the