Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    May 2001
    Location
    Omaha, NE USA, Nebraska, USA
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Printing worksheets in a Workbook (Excel 97/SR2)

    I have written a procedure that is supposed to cycle through all of the worksheets in the active workbook and print any that meet the criteria in the code. I keep getting a run time error when running this procedure. Could someone review my procedure and see if I am missing anything. I have been able to use the oSheet.PrintOut method in other procedures with success, but I can't seem to get it to work in this one.

    <pre>Sub PrtWksht()
    ' Created by Steven M Henderson on August 21, 2001
    ' This procedure will cycle through the worksheets of the active
    ' workbook and print all of the CPWU worksheets ignoring the
    ' built in worksheets used to create everything else
    Dim oSheet As Worksheet
    For Each oSheet In Worksheets
    If oSheet.Name <> "Template" _
    And oSheet.Name <> "RCList" _
    And oSheet.Name <> "Template(2001)" _
    And oSheet.Name <> "List" _
    And oSheet.Name <> "DataLibrary" _
    And oSheet.Name <> "DataLib" Then
    oSheet.PrintOut
    Else
    'fall through by design
    End If
    Next oSheet
    End Sub
    </pre>


    I have also attached a screenshot of the error message.

    Thanks

  2. #2
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Brussels, Brussel, Belgium
    Posts
    159
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Printing worksheets in a Workbook (Excel 97/SR2)

    hi Steve,

    you probably have a hidden sheet somewhere that matches the criteria.

    either you set those to visible before you print them (oSheet.visible=true) or you don't print them at all :

    if osheet.visible then osheet.printout

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Printing worksheets in a Workbook (Excel 97/SR2)

    I found a really handy piece of code that will go thru your workbook and create a custom print sheet dialog box with checkboxes for each sheet so that users can pick and choose what they want to print. You will have to modify it to suit your needs but I am real happy with it:

    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 And CurrentSheet.Name <> "Select a Follow Up" Then <font color=448800>' test condition to weed out sheets you don't want listed</font color=448800>
    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).Activate
    ActiveSheet.PrintOut
    ' ActiveSheet.PrintPreview 'for debugging
    End If
    Next cb
    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
    Sheets("CRCG Meeting Information").Activate
    End Sub

    works real well. try it out -- you may find it is perfect for your needs.

  4. #4
    2 Star Lounger
    Join Date
    May 2001
    Location
    Omaha, NE USA, Nebraska, USA
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Printing worksheets in a Workbook (Excel 97/SR2)

    Thank you very much. By adding the red code, it now works. I should have known this, as I have another procedure that loops thru all the worksheets and then prints the selected sheets a specified number of times. I cut/pasted the red code from my other procedure.

    <pre>Sub PrtWksht()
    ' Created by Steven M Henderson on August 21, 2001
    ' This procedure will cycle through the worksheets of the active
    ' workbook and print all of the CPWU worksheets ignoring the
    ' built in worksheets used to create everything else
    Dim oSheet As Worksheet
    For Each oSheet In Worksheets
    If oSheet.Name <> "Template" _
    And oSheet.Name <> "RCList" _
    And oSheet.Name <> "Template(2001)" _
    And oSheet.Name <> "List" _
    And oSheet.Name <> "DataLibrary" _
    And oSheet.Name <> "DataLib" Then
    <font color=red>If oSheet.Visible = xlSheetVisible Then
    oSheet.PrintOut
    Else
    'fall through by design
    End If</font color=red>
    Else
    'fall through by design
    End If
    Next oSheet
    Next x
    End Sub
    </pre>


    I appreciate your quick response.

Posting Permissions

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