How to capture the Print button event

[Newbie]

Hello,
I created an application with several sheets:
- Sheet1 is a form with a lot of dropdown lists.
- Sheet2, named "Impression", is the document to be printed with all the
values selected in Sheet1
Sheet 2 is hidden

What I want to do is to capture the Print event, so that the VBA macro print
the document in Sheet2, but nothing else. In fact, my macro prints both
sheets...

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Worksheets("Impression").PageSetup.PrintArea = "$A$1:$H$49"
Worksheets("Impression").PrintOut Copies:=1, Collate:=True
Cancel = False
End Sub

If I set Cancel = True, nothing is printed !

How can I do that ?
Thanks for your help.

 

[Otto Moehrbach]

Be very specific when you say "Print button". There are various ways to
initiate printing and you can get different results from each. Exactly what
are you doing to initiate printing? What are the current print settings
when you do what you do?
The easiest way to convert any print initiation to print only from your code
is to first cancel the print command. You do this by setting Cancel to True
in the first line of your macro. By so doing you are using the print
command to fire the macro and to do nothing else. The remaining code in
your macro will then be executed. You might find that you can't print from
a hidden sheet so you may have to include code to unhide/re-hide the sheet.
Another problem is that the print command in your code will fire the
very macro that holds your code. You don't want this to happen, so bracket
your code with the following two lines:
Application.EnableEvents=False
'Your print code
Application.EnableEvents=True
HTH Otto

 

[Newbie]

Thanks a lot Otto!
That does help

Be very specific when you say "Print button". There are various ways to
initiate printing and you can get different results from each. Exactly
what are you doing to initiate printing? What are the current print
settings when you do what you do?
The easiest way to convert any print initiation to print only from your
code is to first cancel the print command. You do this by setting Cancel
to True in the first line of your macro. By so doing you are using the
print command to fire the macro and to do nothing else. The remaining
code in your macro will then be executed. You might find that you can't
print from a hidden sheet so you may have to include code to
unhide/re-hide the sheet.
Another problem is that the print command in your code will fire the
very macro that holds your code. You don't want this to happen, so
bracket your code with the following two lines:
Application.EnableEvents=False
'Your print code
Application.EnableEvents=True
HTH Otto