ExcelBanter - Available printers in a dropdown-combolist?

I'd let Excel do the work for you. there is an old built-in Printer Setup
dialog that when displayed let's the user change the printer as well as
printer settings. Rather than look through a list of printers and place them
in a listbox/dropdown, I'd simply show the Printer Setup dialog. In the set
of userform routines below, I use a label (lblCurrentPrinter) to display the
current printer. The userform has a command button called cmdPrinter
(labeled "Printer..." on the userform) that when clicked displays the
built-in Printer Setup dialog. If a change is made in that dialog,
lblCurrentPrinter is updated to show the new printer.

I've placed comments in with the code to explain what the userform is
doing...

'Routine that automatically fires when this Userform gets loaded into memory
Private Sub UserForm_Initialize()
'Display the current printer name (and if on a network its network path)
' in a label in this userform
Me.lblCurrentPrinter = Application.ActivePrinter
End Sub

'OK button in the userform
Private Sub cmdOK_Click()
'Hide the userform and removes it from memory
Unload Me
End Sub

'"Printer..." button on Userform
Private Sub cmdPrinter_Click()
'Hide the userform
Me.Hide
'Use a bChangePrinter() function to display the built-in Printer Setup
dialog
' and determine whether a new printer was selected. If a new printer,
update the label
' in the userform to show the current printer
If bChangePrinter() Then Me.lblCurrentPrinter =
Application.ActivePrinter
'Show this userform again
Me.Show
End Sub

'Shows the built-in Printer Setup Dialog and determines if a printer change
was made
Function bChangePrinter()

Dim bChange As Boolean

On Error GoTo Error

'Assume the user will not change the printer
bChange = False

'Show the built-in dialog
'If the user clicks the OK button in the built-in dialog, bChange will
return True
'If the user clicks the Cancel button in the built-in dialog, bChange
will return False
bChange = Application.Dialogs(xlDialogPrinterSetup).Show

Cleanup:

'Pass the results of the built-in dialog to the function's True/False
setting
bChangePrinter = bChange

Exit Function
Error:
MsgBox Err.Number & vbLf & vbLf & Err.Description, vbCritical,
"bChangePrinter"
Resume Cleanup
End Function

--
________________________
Robert Rosenberg
R-COR Consulting Services
Microsoft MVP - Excel

"NorTor" wrote in message
...
Hello,

help needed please :-)

For my current application, I would like the user to be able to select
which printer to use in a userform combolist.

The code should loop to find all available printers, and set the
user-selected one to be active.

Thank you in advance!

Best regards,
-Nortor