I have a workbook that contains multiple sheets of which I would like to automate the printing process for several of them.

I would like to be able to have several buttons on the cover page (just the first worksheet) where I can select to print each of the worksheets (ie 1 button for each) and then another button to print all of them (by all of them I really mean a selection of multiple worksheets).

I would also like to be able to select the printer that is used as on some occasions a normal printer will be used but at other times a pdf writer "printer" will be required.

Is there code that can be used to bring up the print prompt for the individual worksheet buttons? I can code to select the print area but not to bring up the print prompt. I can only set it to print the print area to the default printer.

Also in order to print a series of worksheets (only the first page on each), would it be best to basically run the code for each of the individual ones in another function or is it possible to set the print area over multiple worksheets.

I apologise for the length of the query. Thanks for reading all the way through!
I appreciate any help or ideas

Jon

Hello Jon,

you can use this to bring up the Print Dialog:

Application.Dialogs(8).Show

But as you know you can adjust all of the parameters of the printing process..

Sheets("Sheet1").PrintArea = "$C$7:$D$13"

Sheets("Sheet1").PrintOut Copies:=1, ActivePrinter:= "hp deskjet 9600 series"

etc.

If you need a custom list of your printers, you can grab the list via a function similar to this:

Private Declare Function lstrcpy Lib "kernel32.dll" Alias "lstrcpyA" (ByVal lpString1 As String, ByVal lpString2 As Long) As Long
Private Declare Function lstrlen Lib "kernel32.dll" Alias "lstrlenA" (ByVal lpString As Long) As Long
Private Declare Function EnumPrinters Lib "winspool.drv" Alias "EnumPrintersA" (ByVal flags As Long, ByVal name As String, ByVal Level As Long, pPrinterEnum As Long, ByVal cdBuf As Long, pcbNeeded As Long, pcReturned As Long) As Long
Const PRINTER_ENUM_LOCAL = &H2
Private Type PRINTER_INFO_1
        flags As Long
        pDescription As String
        pName As String
        pComment As String
End Type
Private Sub Form_Load()
    Dim longbuffer() As Long
    Dim printinfo() As PRINTER_INFO_1
    Dim numbytes As Long
    Dim numneeded As Long
    Dim numprinters As Long
    Dim c As Integer, retval As Long
   
    numbytes = 2 ^ 12
    ReDim longbuffer(0 To numbytes / 4) As Long
    retval = EnumPrinters(PRINTER_ENUM_LOCAL, "", 1, longbuffer(0), numbytes, numneeded, numprinters)
    If retval = 0 Then
        numbytes = numneeded
        ReDim longbuffer(0 To numbytes / 4) As Long
        retval = EnumPrinters(PRINTER_ENUM_LOCAL, "", 1, longbuffer(0), numbytes, numneeded, numprinters)
        If retval = 0 Then Debug.Print "Error occured!": Exit Sub
    End If

    If numprinters <> 0 Then ReDim printinfo(0 To numprinters - 1) As PRINTER_INFO_1
    For c = 0 To numprinters - 1
        printinfo©.flags = longbuffer(4 * c)
        printinfo©.pDescription = Space(lstrlen(longbuffer(4 * c + 1)))
        retval = lstrcpy(printinfo©.pDescription, longbuffer(4 * c + 1))
        printinfo©.pName = Space(lstrlen(longbuffer(4 * c + 2)))
        retval = lstrcpy(printinfo©.pName, longbuffer(4 * c + 2))
        printinfo©.pComment = Space(lstrlen(longbuffer(4 * c + 3)))
        retval = lstrcpy(printinfo©.pComment, longbuffer(4 * c + 3))
    Next c
'
'   Print out each name of each printer
    For c = 0 To numprinters - 1
        Debug.Print "Printer " & c + 1 & ": "; printinfo©.pName
    Next c
End Sub

Hope this helps at least a bit (IMG:http://www.utteraccess.com/forum/style_emoticons/default/smile.gif)

Martin