vba get in combobox all printers list

[nd0911]

hi

can I get a code that return all printers in a combobox or listbox ?

TNX

 

[Rick Rothstein]

You did not say what kind of ListBox you had or where it was located, so I have given you code for all three possibilities...

[table="width: 500"]
[tr]
	[td][COLOR="#008000"]'Forms Control ListBox on the active sheet
'------------------------------------------------------------------------[/COLOR]
Sub PrintersToFormsListBox()
  Dim X As Long, Printers As Object, arrPrinters As Variant
  With CreateObject("WScript.Network")
    For X = 1 To .EnumPrinterConnections.Count Step 2
      ActiveSheet.Shapes("List Box 1").ControlFormat.AddItem .EnumPrinterConnections(X)
    Next
  End With
End Sub[/td]
[/tr]
[/table]

[table="width: 500"]
[tr]
	[td][COLOR="#008000"]'ActiveX Control ListBox on the active sheet
'------------------------------------------------------------------------[/COLOR]
Sub PrintersToActiveXListBox()
  Dim X As Long, Printers As Object, arrPrinters As Variant
  With CreateObject("WScript.Network")
    For X = 1 To .EnumPrinterConnections.Count Step 2
      ActiveSheet.ListBox1.AddItem .EnumPrinterConnections(X)
    Next
  End With
End Sub[/td]
[/tr]
[/table]

[table="width: 500"]
[tr]
	[td]
[COLOR="#008000"]'ListBox on a UserForm and assumes
'CommandButton1 loads the ListBox 
'------------------------------------------------------------------------[/COLOR]
Private Sub CommandButton1_Click()
  Dim X As Long, Printers As Object, arrPrinters As Variant
  With CreateObject("WScript.Network")
    For X = 1 To .EnumPrinterConnections.Count Step 2
      ListBox1.AddItem .EnumPrinterConnections(X)
    Next
  End With
End Sub[/td]
[/tr]
[/table]

 

[nd0911]

thanks Rick, the last code, was what I was looking for

TNK!!

 

[nd0911]

hello again Rick

I have a littel problem with the code becouse when I choose then the printer from the listbox and click Ok I add a line of code :

application.activeprinter = listbox.value

but I get a run time 1004 error

do you know what should I do ?

thank you !

 

[Rick Rothstein]

hello again Rick

I have a littel problem with the code becouse when I choose then the printer from the listbox and click Ok I add a line of code :

application.activeprinter = listbox.value

but I get a run time 1004 error

do you know what should I do ?

So you are showing the printer list in order to let the user set the active printer, correct? If so, do you absolutely have to use a UserForm to do it? I ask because Excel's VBA has a dialog box you can popup and let the user set the active printer from there... it takes just one line of code to do it.

Application.Dialogs(xlDialogPrinterSetup).Show

 

[nd0911]

hello Rick,

thank you for your quick answer, I know all about Application.Dialogs(xlDialogPrinterSetup).Show
but I need it with the listbox method...

 

[zekejenkins]

So you are showing the printer list in order to let the user set the active printer, correct? If so, do you absolutely have to use a UserForm to do it? I ask because Excel's VBA has a dialog box you can popup and let the user set the active printer from there... it takes just one line of code to do it.

Application.Dialogs(xlDialogPrinterSetup).Show

Hi

You did not say what kind of ListBox you had or where it was located, so I have given you code for all three possibilities...

[table="width: 500"]
[tr]
    [td][COLOR="#008000"]'Forms Control ListBox on the active sheet
'------------------------------------------------------------------------[/COLOR]
Sub PrintersToFormsListBox()
  Dim X As Long, Printers As Object, arrPrinters As Variant
  With CreateObject("WScript.Network")
    For X = 1 To .EnumPrinterConnections.Count Step 2
      ActiveSheet.Shapes("List Box 1").ControlFormat.AddItem .EnumPrinterConnections(X)
    Next
  End With
End Sub[/td]
[/tr]
[/table]

[table="width: 500"]
[tr]
    [td][COLOR="#008000"]'ActiveX Control ListBox on the active sheet
'------------------------------------------------------------------------[/COLOR]
Sub PrintersToActiveXListBox()
  Dim X As Long, Printers As Object, arrPrinters As Variant
  With CreateObject("WScript.Network")
    For X = 1 To .EnumPrinterConnections.Count Step 2
      ActiveSheet.ListBox1.AddItem .EnumPrinterConnections(X)
    Next
  End With
End Sub[/td]
[/tr]
[/table]

[table="width: 500"]
[tr]
    [td]
[COLOR="#008000"]'ListBox on a UserForm and assumes
'CommandButton1 loads the ListBox
'------------------------------------------------------------------------[/COLOR]
Private Sub CommandButton1_Click()
  Dim X As Long, Printers As Object, arrPrinters As Variant
  With CreateObject("WScript.Network")
    For X = 1 To .EnumPrinterConnections.Count Step 2
      ListBox1.AddItem .EnumPrinterConnections(X)
    Next
  End With
End Sub[/td]
[/tr]
[/table]

Hi Rick, this was wonderful for what I needed; however, I'm wondering is there's a way to get the ports in the list as well?