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!