[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]
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.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 ?
HiSo 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 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?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...
Code:[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]
Code:[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]
Code:[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]