How to stop printing after the before_print event

Hi Everybody

I have a Before_Print event procedure in one of my reports. This document contains a large number of worksheets. The user can select one ord more worksheets and print them if they want to.

In the Before_Print event procedure, all I essentially do is set up the report titles, margin setups etc etc. - nothing particularly spectacular - butwhat is happening is that it always sends the document to the printer to print even if I have only clicked on the built-in Print Preview Icon. Clicking on the built-in Print Icon also prints the document whether or not I want it to be printed.

A snippet of the code is reproduced below :-

[vba]
Private Sub Workbook_BeforePrint(Cancel As Boolean)
...
...
'Some code here
sheetCountStart = 0: sheetCountWrkg = sheetCountStart
Do While sheetCountWrkg <= UBound(selectedSheetsArray)
If sheetCountWrkg = sheetCountStart Then
Worksheets(selectedSheetsArray(sheetCountWrkg)).Select
Else
Worksheets(selectedSheetsArray(sheetCountWrkg)).Select Replace:=False
End If
sheetCountWrkg = sheetCountWrkg + 1
Loop
Application.ScreenUpdating = True
ActiveWindow.SelectedSheets.PrintPreview'Removing this line doesn't even show the preview
Application.ScreenUpdating = False

...
...
'Some code here
End Sub
[/vba]

Removing print preview statement doesn't show the print preview at all but prints the document nevertheless!

With the print preview statement in there, when I click on the built-in Print Preview Icon, it shows the preview twice and then prints the document but when I click on the built-in Print Icon, it shows the preview once and then prints the document.

Any suggestions what have I got wrong?

As a get around I have made the above Before_Print event procedure in to a Custom_Printing procedure - this however is not my preferred option.


Best regards



Deepak Agarwal

It prints because it's a Before_Print event. User commands to print, but before that, you command it to do what you want...it still has the print command so when your code is done, it will print unless you tell it not to in your code. The Before_Print event should set Cancel as Boolean (at the top) if you don't want it to print then:

[VBA]Cancel = True[/VBA]

jproffer

In that case, why does it print when I click on the built-in PrintPreview button?

Also,is there a way to tell if this event has been initiated by the click on 'so-and-so' button and how can I get some of the properties of the button that invoked the event procedure?

Best regards


Deepak