Results 1 to 7 of 7
  1. #1
    4 Star Lounger
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    543
    Thanks
    56
    Thanked 0 Times in 0 Posts

    Select and print defined areas of spreadsheets

    Hi

    I am wanting print selected ares of specified worksheets in a workbook.

    I came across this code which allows me to select the worksheets to print but does not allow me to specify the area to print.

    Option Explicit


    Sub SelectSheets()
    Dim i As Integer
    Dim TopPos As Integer
    Dim SheetCount As Integer
    Dim PrintDlg As DialogSheet
    Dim CurrentSheet As Worksheet
    Dim cb As CheckBox
    Application.ScreenUpdating = False

    ' Check for protected workbook
    If ActiveWorkbook.ProtectStructure Then
    MsgBox "Workbook is protected.", vbCritical
    Exit Sub
    End If

    ' Add a temporary dialog sheet
    Set CurrentSheet = ActiveSheet
    Set PrintDlg = ActiveWorkbook.DialogSheets.Add

    SheetCount = 0

    ' Add the checkboxes
    TopPos = 40
    For i = 1 To ActiveWorkbook.Worksheets.Count
    Set CurrentSheet = ActiveWorkbook.Worksheets(i)
    ' Skip empty sheets and hidden sheets
    If Application.CountA(CurrentSheet.Cells) <> 0 And _
    CurrentSheet.Visible Then
    SheetCount = SheetCount + 1
    PrintDlg.CheckBoxes.Add 78, TopPos, 150, 16.5
    PrintDlg.CheckBoxes(SheetCount).Text = _
    CurrentSheet.Name
    TopPos = TopPos + 13
    End If
    Next i

    ' Move the OK and Cancel buttons
    PrintDlg.Buttons.Left = 240

    ' Set dialog height, width, and caption
    With PrintDlg.DialogFrame
    .Height = Application.Max _
    (68, PrintDlg.DialogFrame.Top + TopPos - 34)
    .Width = 230
    .Caption = "Select sheets to print"
    End With

    ' Change tab order of OK and Cancel buttons
    ' so the 1st option button will have the focus
    PrintDlg.Buttons("Button 2").BringToFront
    PrintDlg.Buttons("Button 3").BringToFront

    ' Display the dialog box
    CurrentSheet.Activate
    Application.ScreenUpdating = True
    If SheetCount <> 0 Then
    If PrintDlg.Show Then
    For Each cb In PrintDlg.CheckBoxes
    If cb.Value = xlOn Then
    Worksheets(cb.Caption).Select Replace:=False
    End If
    Next cb
    ActiveWindow.SelectedSheets.PrintOut copies:=1
    ActiveSheet.Select
    End If
    Else
    MsgBox "All worksheets are empty."
    End If

    ' Delete temporary dialog sheet (without a warning)
    Application.DisplayAlerts = False
    PrintDlg.Delete

    ' Reactivate original sheet
    CurrentSheet.Activate
    End Sub

    Is there a way to call up the areas specified in "View|Custom Views", set the print area based on the view for each worksheet? I am working with 9 worksheets and 1 worksheet has 14 "Custom View" areas defined.
    cheers

    Phil Carter

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    If each view has a defined print area (the print area and sheet are both saved with the view), you can just loop through each view and print the activesheet:
    Code:
    Sub PrintViews()
    Dim cv As CustomView
    For Each cv In ActiveWorkbook.CustomViews
    cv.Show
    ActiveSheet.PrintOut
    Next
    End Sub
    Steve
    Last edited by sdckapr; 2012-11-21 at 09:21. Reason: fixed code formatting

  3. The Following User Says Thank You to sdckapr For This Useful Post:

    bonriki (2012-11-20)

  4. #3
    4 Star Lounger
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    543
    Thanks
    56
    Thanked 0 Times in 0 Posts
    Steve thanks for that

    Firstly, I am having trouble getting the Custom Views to stick. Page layout keeps reverting to Automatic for page width and height. I don't want to print over multiple pages!

    On the page where I have 14 CustomViews set I get 14 copies of 1 of the pages. However, if I comment out the ActiveSheet.PrintOut line the code cycles through all the required CustomViews.
    cheers

    Phil Carter

  5. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Could you attach an example file demonstrating the problem? When I set the custom view the print settings are stored with the view and that includes the page dimensions.

    Steve

  6. #5
    4 Star Lounger
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    543
    Thanks
    56
    Thanked 0 Times in 0 Posts
    Steve
    I have managed to figure out how to strip all the sensitive info out, thus
    decreasing to size to 500Mb rather than 11.5Mb
    This file is still quite
    sensitive
    Attached Files Attached Files
    cheers

    Phil Carter

  7. #6
    4 Star Lounger
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    543
    Thanks
    56
    Thanked 0 Times in 0 Posts
    Steve hi
    Did you have any joy with my little problem?

    I have managed to determine what the problem was. The main problem is that I set the page layout for each page but this is ot sticking and the layout reverts to a specific , B441:P485, and prints this for all custom views!

    Not really what I want.

    How can I get the page layout settings to stick for each custom view?
    cheers

    Phil Carter

  8. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    You should be able to save the page layout settings in a custom view.

    Steve

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •