Send userform behind Print Preview page

[Peter Rattigan]

I wish to run this macro from a command button on a userform. When it runs it opens the correct sheet for a print preview but it opens it behind the userform and locks up the code which is still running but can't be stopped.
What would I need to add to this code to bring the print preview to the front and leave the userform available once the print preview is closed down? Any help appreciated thanks.

Sub PrintSC1()
'
' PrintSC1 Macro


'
    Application.ScreenUpdating = False
    Sheets("Scenario 1").Visible = True
    MsgBox "You have selected the Preview for Scenario 1. If you are happy with it select PRINT. If you wish to make changes select CLOSE.", , "Scenario 1 Preview"
    Sheets("Scenario 1").Select
    ActiveWindow.SelectedSheets.PrintPreview
    Sheets("Scenario 1").Visible = False
    Sheets("frontpage").Select
    Application.ScreenUpdating = True
    
End Sub

 

[BrianB]

This works Ok for me :-

Private Sub CommandButton1_Click()
    Me.Hide
    ActiveSheet.PrintPreview
    Me.Show
End Sub

 

[Andrew Poulsom]

This worked for me:

' General module

Sub ShowForm()
    UserForm1.Show vbModeless
End Sub

' UserForm module

Private Sub CommandButton1_Click()
    Application.ScreenUpdating = False
    Sheets("Scenario 1").Visible = True
    MsgBox "You have selected the Preview for Scenario 1. If you are happy with it select PRINT. If you wish to make changes select CLOSE.", , "Scenario 1 Preview"
    Sheets("Scenario 1").Select
    Me.Hide
    ActiveWindow.SelectedSheets.PrintPreview
    Sheets("Scenario 1").Visible = False
    Sheets("frontpage").Select
    Application.ScreenUpdating = True
    Me.Show
End Sub

Note that showing a UserForm modelessly requires Excel 2000 or later.

 

[Peter Rattigan]

Thanks everyone. Works a treat.

Cheers