Print preview

[ManvinderKaur]

Hi I am using following code for print preview of spreadsheet.

'command button to print site survey water worksheet
Private Sub cmdPrintSiteSurveyWater_Click()
On Error GoTo ErrorLine
Dim SiteSurveyWater As Integer
' message box to prompt print out
SiteSurveyWater = MsgBox("Do you want to take print out of Site Survey Water Sheet", vbYesNo + vbInformation, "Print Out")
If SiteSurveyWater = vbYes Then

With Worksheets("3.1 Site Survey Water Print")
.Visible = True
.Activate

With Worksheets("3.1 Site Survey Water Print").PageSetup

.PrintTitleRows = "$1:$3"
.PrintTitleColumns = ""
.PrintArea = "$A$1:$I$37"
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = "Page &P"
.CenterFooter = ""
.RightFooter = "&T &D"
.LeftMargin = Application.InchesToPoints(0.26)
.RightMargin = Application.InchesToPoints(0.26)
.TopMargin = Application.InchesToPoints(0.4)
.BottomMargin = Application.InchesToPoints(0.4)
.HeaderMargin = Application.InchesToPoints(0.3)
.FooterMargin = Application.InchesToPoints(0.3)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.Zoom = 77

End With

.PrintPreview

End With
End If
Exit Sub
ErrorLine:
MsgBox ("Error Number:" & Err.Number & ". " & Err.Description & ". Source:" & Err.Source)
ActiveWorkbook.Close
End Sub


it is very slow... take 30 sec to display print preview....how come I modify this?...

 

[Andrew Poulsom]

Do you need all that PageSetup code (which can be slow)? If all those properties have already been set there is no point in setting them again.

 

[ManvinderKaur]

okay thanks....the opened spreadsheet becomes visible..once the public see print preview...Is this possible ?...if they see print preview and then take print out and then that sheet again becomes hidden?....is it?.....I can not figure that out...

 

[Andrew Poulsom]

Try:

Private Sub cmdPrintSiteSurveyWater_Click_Click()
    On Error GoTo ErrorLine
    Dim SiteSurveyWater As VbMsgBoxResult
'   Message box to prompt print out
    SiteSurveyWater = MsgBox("Do you want to take print out of Site Survey Water Sheet", vbYesNo + vbQuestion, "Print Out")
    If SiteSurveyWater = vbYes Then
        With Worksheets("3.1 Site Survey Water Print")
            .Visible = True
            .PrintPreview
            .Visible = False
        End With
    End If
    Exit Sub
ErrorLine:
    MsgBox ("Error Number:" & Err.Number & ". " & Err.Description & ". Source:" & Err.Source)
    ActiveWorkbook.Close
End Sub

 

[ManvinderKaur]

thank you very very much...That worked...yehhhh...
Thanks heeps....I am new to VBA..