Print preview

ManvinderKaur

Board Regular
Joined
Jun 16, 2010
Messages
149
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?...
 

Some videos you may like

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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

Board Regular
Joined
Jun 16, 2010
Messages
149
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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Try:

Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,099,775
Messages
5,470,709
Members
406,718
Latest member
waseem11

This Week's Hot Topics

Top