+ Reply to Thread
Results 1 to 2 of 2

Macro shows list of worksheets to print but the list goes off desktop and not all visible.

  1. #1
    Registered User
    Join Date
    01-09-2015
    Location
    usa
    MS-Off Ver
    2007
    Posts
    0

    Macro shows list of worksheets to print but the list goes off desktop and not all visible.

    I got this code to print multiple worksheets. It creates a checkbox and list all the worksheets, but my list is so long it goes off of the desktop and is no longer visible. Please help.


    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).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
    CurrentSheet.Activate
    End Sub
    NOTE: Aaron Blood suggested the following modification, which prints the selected sheets as a single print job. This allows the sheet to be printed with continuous page numbers.

    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

  2. #2
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166
    Hello tbenitez0801,

    Welcome to Excelforum. Be a part of large Excel community. Enjoy Learning.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Data validation list automatically shows the first item in the list
    By Alyena in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-15-2013, 07:00 AM
  2. [SOLVED] Need to take a list of names and expand them so the list shows the same name 5 times...
    By 1OneSeven7 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 07-24-2012, 01:33 PM
  3. Replies: 1
    Last Post: 06-22-2012, 11:53 AM
  4. Need a macro that will cycle through list then filter, subtotal, and print the list
    By Crimson Bourne in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-26-2012, 12:53 PM
  5. [SOLVED] Can I print a list of all of the worksheets within a workbook?
    By SRsonn in forum Excel General
    Replies: 1
    Last Post: 06-29-2005, 03:05 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1