setting 2 printers via vba...

[RobbieC]

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:

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:

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

 

[Sektor]

Can't you hard-code them into those cells?

 

[RobbieC]

No, because these are my printers. Other users will have different printers which they will have to specify

 

[Norie]

RobbieC

What exactly are you trying to do?

Do you want to return the name of the printer the user selected in the dialog?

 

[RobbieC]

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:

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

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

 

[Sektor]

You could get printers with WMI, feed them to some list box (on the sheet, on the UserForm) and let users select:

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

 

[RobbieC]

Thanks Sektor, looks good to me

Happy new year to you!

 

[Sektor]

Happy New Year too!