Intercept printing on Excel not working properly

[max76]

Hi everyone,

I'm using Excel 365 and I'm trying to use the BeforePrint event to intercept printing to automate this process in a way that the user does not have to manually choose which paper to print to (in PDF). I created a subroutine that detects the last page with some text on it and will assign the page number to a variable that I want to use in the Printout method.


ActiveSheet.PrintOut From:=1, To:=PrintToPage
Cancel = True


The problem is that when I use the Cancel = True (since I expected the printing process to be completed by the Printout method) no printing occurs at all. If I delete "Cancel = True" the printing is done correctly (using the page number assigned to my variable), but after the printing is executed, a print dialogue box appears asking the settings to print again.

What am I doing wrong?

Thank you for your support!
Regards

Massimo Ollani

[Paul_Hossler]

​I did not use a Class, but just put this in the Workbook module

See if this does what you want



Option Explicit


Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim PrintToPage As Integer
'I compiled a subroutine to find out the last page with text in it which is the last page to be printed
'The page number is stored in the variable PrintTo Page
'Let's assume the last page to print resulting from the subroutine is 3


PrintToPage = 1


ActiveSheet.PrintOut From:=1, To:=PrintToPage
Cancel = True
End Sub

[max76]

Hi Paul,

Thank you for your reply. However, your solution does not work. When I click to Print NO printing process is initiated at all. That's why I turned to the approach using the Class. But even with that approach, the printing process is either started TWICE or not all all (depending on the value that CANCEL gets: True or False). It's hard to believe that Microsoft made such an easy automation like adjusting the printing process to specific settings can be so hard to accomplish. I hope to get the solution I've been looking for.

Awaiting further support
Thanks

Massimo

[Aflatoon]

Disable events while printing:


PrintToPage = 3
on error resume next
Application.EnableEvents = False
ActiveSheet.PrintOut From:=1, To:=PrintToPage
Application.EnableEvents = True
on error goto 0
Cancel = True

[arnelgp]

you can also add code provided by ChatGPT.
before running the code, comment out your code on Work_BeforePrint().
copy and run this code:



' chatgpt
'
Sub DeterminePagesBeingPrinted()
Dim ws As Worksheet
Dim pageCount As Integer
Dim i As Integer
Dim rng As Range

' Set the worksheet and range to be printed
Set ws = ThisWorkbook.Sheets("Foglio1") ' Change "Sheet1" to your sheet name
Set rng = ws.UsedRange ' Or define a specific range

' Use the PageSetup to calculate total pages
With ws.PageSetup
.Zoom = False ' Ensure FitToPagesWide/High works
.FitToPagesWide = 1
.FitToPagesTall = False ' Set to False if you want height unrestricted
End With

' Calculate total pages
pageCount = ws.HPageBreaks.Count + 1 ' Horizontal pages
pageCount = pageCount * (ws.VPageBreaks.Count + 1) ' Multiply by vertical pages

' Output total pages
MsgBox "Total pages to print: " & pageCount

' Simulate printing and determine the pages being printed
For i = 1 To pageCount
' Print preview one page at a time
rng.PrintOut From:=i, To:=i, Preview:=True ' Change Preview to False to actually print
MsgBox "Currently printing page: " & i
Next i
End Sub

[max76]

Hi Aflatoon,

Thank you very very much for your support!!! Your piece of code worked like a charm. I learned a useful method!

Thank you again!
Massimo

[max76]

Thank you arnelgp (http://www.vbaexpress.com/forum/member.php?74556-arnelgp)!