Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    Jan 2010
    Location
    Central New York
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Forcing Excel to ask what printer to use everytime you print

    Any way to force Excel to ask what printer to use when you go to print? I sometimes print to a plotter, then while Excel still open, I open a file that I want to print to a 8 1/2 X 11 sheet. I sometimes forget to change printers.

    Thanks
    George

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    George,

    Use File, Print vs the Icon or Ctrl+P then you'll always get the settings page.

    Setup a VBA procedure in your Personal.xls file to capture the BeforePrint event. It it a Workbook level event so it needs to go in the Microsoft Excel Objects, ThisWorkbook module. HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    New Lounger
    Join Date
    Jan 2010
    Location
    Central New York
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    That's how I normally do it. But since I don't normally use the plotter, I don't pay attention when I want it to go to the default printer. What does the VBA procedure do?
    Thanks for the response.
    George

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    George,

    Here's an example probablly needs to be tweaked to your circumstances but should give you an idea.
    Code:
    Option Explicit
    
    Private Sub Workbook_BeforePrint(Cancel As Boolean)
    
    'Note: This will prompt you for each sheet in the Workbook!
    '      If you select the NO button printing will continue.
    '      If you select the YES button printing will cease allowing
     
     Dim wks  As Worksheet
     Dim iAns As Integer
     
     For Each wks In ActiveWorkbook.Sheets
     
       iAns = MsgBox("Do you Want to use the Plotter for" & vbCrLf & _
                     "Worksheet: " & wks.Name & "?", _
                     vbYesNo + vbQuestion, "Checking for Plotter Use")
                       
       If iAns = vbYes Then
         Cancel = True
         Exit Sub   'Stop Printing
       Else
         Cancel = False
       End If
       
     Next
     
    End Sub
    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. #5
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,640
    Thanks
    115
    Thanked 651 Times in 593 Posts
    If all else fails, a different alternative is to do all your printing using a custom printer menu userform where you must select the printer then click a print button. You can also assign the macro to a menu icon or a key combination. Here is a very simple example where you select between 2 printers to use: a local and a networked printer.

    Printer.jpg

    Code to activate the userform in the PrintButton_Click event subroutine
    Code:
    Private PrintButton_Click()
    Userform1.show
    end sub

    The code for the userform is:
    Code:
    Private Sub CommandButton1_Click()
    
    If TextBox1.Value = 1 Then
        ActiveWindow.SelectedSheets.PrintOut ActivePrinter:="Canon MX880 series Printer on Ne02:"
    ElseIf TextBox1.Value = 2 Then
        ActiveWindow.SelectedSheets.PrintOut ActivePrinter:="\\Maudibe\HP DeskJet 882C on Ne06:"
    End If
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
            IgnorePrintAreas:=False
    UserForm1.Hide
    End Sub
    To find the name of your printers to use, activate the printer in the print menu then run the following code. The name to use will show in the cell. Copy/paste into the code. Repeat for each printer you wnat to choose from.

    Code:
    Public Sub FindPrinterName()
    Cells(1, 2).Value = Application.ActivePrinter
    End Sub
    HTH,
    Maud
    Last edited by Maudibe; 2013-05-19 at 20:42.

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    @RG
    You need an application-level event. A workbook event in the personal workbook would only work if you printed the personal macro workbook.
    Regards,
    Rory

    Microsoft MVP - Excel

  7. #7
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Rory,

    Good Point! Thanks! I'm claiming drug induced lapse from recent surgery.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

Posting Permissions

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