Populate printer list in combobox

**dorkydancer** · 05-25-2012, 05:37 PM

I'm trying to print a specific area of a chosen sheet. It automatically prints to file, but I want the user to be able to choose which printer to print to.
This sub opens the printing form:

Sub SEM_PrintReport()

    If MenuBars(xlWorksheet).Menus("SEM Tracking").MenuItems("Print Report").Enabled = False Then
        Beep
        Exit Sub
    End If
    
    SEM_PrintDialog.Show

End Sub

This is in SEM_PrintDialog form

Private Sub UserForm_Initialize()
' Set up the list of sheet names when this form is loaded.
Dim sh As Worksheet
Dim objPrinter As Printer

    cboSheets.Clear
    For Each sh In ActiveWorkbook.Worksheets
        If sh.Range("$A$1").Text Like "Monthly Tracking for*" Then
            cboSheets.AddItem sh.name
        End If
    Next sh
    cboSheets.ListIndex = cboSheets.ListCount - 1
    
    cboPrinter.Clear
    For Each objPrinter In Printers
        cboPrinter.AddItem objPrinter.DeviceName
    Next
    cboPrinter.ListIndex = cboPrinter.ListCount - 1

End Sub

This sub will be called by the printing form and prints the specific area printArea

Sub SEM_Print(ByVal name As String)
Dim printArea As Range

    
    Windows("MonthlySEMReport.xls").Activate
    Worksheets(name).Activate
    Set printArea = Range("A3:H96")
    printArea.PrintOut

End Sub

The code to populate the list of printers is not working. It doesn't seem to like