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