Results 1 to 3 of 3

tweeking macro to print to specific printer (non default)

Threaded View

  1. #1
    Forum Contributor
    Join Date
    10-18-2010
    Location
    Walla Walla, Washington
    MS-Off Ver
    Excel 2007
    Posts
    124

    Cool tweeking macro to print to specific printer (non default)

    I have a macro I found on this site that I really like. It helps me easily pick select different sheets in the workbook (using check boxs) and sends them to a printer when executed. The only quam I have with the macro is it would be great if one were able to send them to a pre determined printer (other than my normal default printer). I have a PDF creator that is assigned as a printer so it would be nice to have the selected sheets print to that printer versus my default. My goal is to minimize steps in printing. Are there options built into excel that will allow me to do this or will I have to create a new macro or add to the existing one. The marco I have in question is the following, thanks for any and all 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
    Last edited by Leith Ross; 10-19-2010 at 11:13 PM. Reason: Added Code Tags

Thread Information

Users Browsing this Thread

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

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