setting 2 printers via vba...

RobbieC

Active Member
Joined
Dec 14, 2016
Messages
376
Office Version
  1. 2010
Platform
  1. Windows
Hi there, I have several printers and I want to be able to define 2 separate printers for 2 different tasks within Excel via vba and display them within a couple of cells...

I can activate the 'select printer' dialog box using:
Code:
Sub SetupReportPrinter(control As IRibbonControl)
    Application.Dialogs(xlDialogPrinterSetup).Show
End Sub


Sub SetupLabelPrinter(control As IRibbonControl)
    Application.Dialogs(xlDialogPrinterSetup).Show
End Sub
and I want to send this info to Sheets("Project").Range("I15") & Sheets("Project").Range("I17") respectively

Then when I click my 'print' buttons which currently are hardcoded:

Code:
Sub PrintRecord(control As IRibbonControl)  
  On Error Resume Next
  With Worksheets("Record")
    '.Activate
    .PrintOut Preview:=True, ActivePrinter:="iR C2380"
  End With
End Sub


Sub PrintLabels(control As IRibbonControl)
  On Error Resume Next
  With Worksheets("Labels")
    '.Activate
    .PrintOut Preview:=True, ActivePrinter:="LabelPrinter"
  End With
End Sub
to change each of the ActivePrinter:= to the value of Sheets("Project").Range("I15") & Sheets("Project").Range("I17") respectively

I'm not sure if I've explained this particularly well, but I'm looking for a way for the user to define the 2 separate printers which are then activated by the 2 print buttons

If you can point me in the right direction, that'd be splendid :)
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
Can't you hard-code them into those cells?
 
Upvote 0
No, because these are my printers. Other users will have different printers which they will have to specify
 
Upvote 0
RobbieC

What exactly are you trying to do?

Do you want to return the name of the printer the user selected in the dialog?
 
Upvote 0
Hi Norie, yes. I want the user to be able to define 2 printers into the 2 cells

and then the code on my print buttons will look like:
Code:
Sub PrintRecord(control As IRibbonControl)  On Error Resume Next
  With Worksheets("Record")
    '.Activate
    .PrintOut Preview:=True, ActivePrinter:="USER DEFINED PRINTER 1 (from the first cell I15)"
  End With
End Sub

Sub PrintLabels(control As IRibbonControl)
  On Error Resume Next
  With Worksheets("Labels")
    '.Activate
    .PrintOut Preview:=True, ActivePrinter:="USER DEFINED PRINTER 2 (from the second cell I17)"
  End With
End Sub

I can get the name of the 'default' printer into the cell using
Code:
Public Function DefaultPrinter() As String    DefaultPrinter = Application.ActivePrinter
End Function

Private Sub Workbook_Open()


  'Set DefaultPrinter to Project sheet
    Sheets("Project").Range("I17").Formula = "=DefaultPrinter()"
 
End Sub
but I want the user to be able to choose (and display on the screen) 2 different printers :)
 
Upvote 0
You could get printers with WMI, feed them to some list box (on the sheet, on the UserForm) and let users select:
Code:
Sub Sub6()
    Dim wmi, printer, printers
    Set wmi = CreateObject("winmgmts:\\.\root\cimv2")
    Set printers = wmi.ExecQuery("SELECT * FROM Win32_Printer")
    For Each printer In printers
        MsgBox printer.Name
    Next
End Sub
 
Upvote 0
Thanks Sektor, looks good to me :)

Happy new year to you!
 
Upvote 0
Happy New Year too!
hi.gif
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,186
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top