Resuming code after closing a print preview

[DarrenW]

Hello all,

I am using the following code in to call a print preview from a
userform

Private Sub JobPrint()

Me.Hide
ThisWorkbook.Sheets("PSheet").PrintPreview
Me.Show

End Sub

When I click on the print button on my userform a sub "builds" a
worksheet that contains all the information I want to print. The sub
then hides the userform and calls a print preview of the worksheet.

When I close the print preview (by clicking the Close button) and
Excel shows the userform again, it doesn't return to the print sub
from which it was called, so the print sub which called the print
preview does'nt complete its execution.

Any ideas or help would be great.

Thanks in advance.

DarrenW

 

[Bob Kilmer]

Darren,
(Modeless userforms were introduced with Excel 2000, so this advice may not
apply to you if you are using an earlier version.)

Me.Show opens the userform modally - that is, it requires the user to
acknowledge or dismiss the dialog before code execution continues.

Me.Show is equivalent to Me.Show vbModal

Either use Me.Show vbModeless, which may allow code execution to continue,
or open the form later, after the print code finishes.

Bob Kilmer

 

[Cakadola]

Hi Bob,

I just try your code and it works well.

Private Sub CmdCTK_Click()

Me.Hide

' ThisWorkbook.Sheets("PSheet").PrintPreview

ActiveSheet.PrintPreview ' we can use this code when we need to preview another sheet

Me.Show (vbModeless)

End Sub


Thank you.
Cakadola