Print a selected number of sheets

[RobN]

I have a number of sheets in a workbook and a selection of 4 need to be
printed on a regular basis. Each of those sheets have been formatted to
print a selected area.
What I would like (and I already some code, as below), is to amend the code
so that it will print to PDF but only ask for a file name once. ie it will
print the selected sheets into the one PDF document.

Rob

Option Explicit

Private Sub CancelButton_Click()
Unload Me
End Sub

Private Sub OKButton_Click()
Dim i As Long
Application.ActivePrinter = "Adobe PDF on Ne06:"

If ListBox1.Selected(0) = False And _
ListBox1.Selected(1) = False And _
ListBox1.Selected(2) = False And _
ListBox1.Selected(3) = False Then
MsgBox "Select from the list.", vbInformation
Exit Sub
End If

' choose an item
Me.Hide
For i = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(i) Then
Worksheets(ListBox1.List(i)).PrintOut From:=1, To:=1
End If
Next i
'Me.Show

Dim FoundOne As Boolean

FoundOne = False
For i = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(i) Then
FoundOne = True
Exit For
End If
Next i

Unload Me
End Sub

Private Sub UserForm_Initialize()
With ufPrintNo.ListBox1
.RowSource = ""
.AddItem Sheet3.Name
.AddItem Sheet9.Name
.AddItem Sheet2.Name
.AddItem Sheet10.Name
.MultiSelect = fmMultiSelectMulti
End With

End Sub

 

[Dave Peterson]

You can loop through those items in the list and build an array of all the
selected items. Then print those worksheets whose names are placed in that
array:

Option Explicit
Private Sub CancelButton_Click()
Unload Me
End Sub
Private Sub OKButton_Click()
Dim lCtr As Long
Dim sCtr As Long
Dim mySheets() As String
ReDim mySheets(1 To Me.ListBox1.ListCount)

Application.ActivePrinter = "Adobe PDF on Ne06:"

sCtr = 0
With Me.ListBox1
For lCtr = 0 To .ListCount - 1
If .Selected(lCtr) Then
sCtr = sCtr + 1
mySheets(sCtr) = .List(lCtr)
End If
Next lCtr
End With

If sCtr = 0 Then
'nothing selected
Beep
Else
Me.Hide
ReDim Preserve mySheets(1 To sCtr)
Worksheets(mySheets).PrintOut preview:=True
'Me.Show
End If

Unload Me
End Sub
Private Sub UserForm_Initialize()
With Me.ListBox1
.RowSource = ""
.AddItem Sheet3.Name
.AddItem Sheet9.Name
.AddItem Sheet2.Name
.AddItem Sheet10.Name
.MultiSelect = fmMultiSelectMulti
End With
End Sub

 

[RobN]

Thanks Dave, I hoped you'd come through for me.
A couple of questions and request for a slight change, please.

1. I discovered this line from running a macro on my computer at work, but I
find I have to change theNe06 to Ne02 on my home computer. Is there
something I can code to always find the PDF writer on any computer?
Application.ActivePrinter = "Adobe PDF on Ne06:"

2. Basically what you have done is GREAT!!, but can some code be added that
will automatically open the Save As box to save it as a PDF (with My Docs
Folder as the default)?
I expect something would need to be added after this line or it be changed
somehow.
Worksheets(mySheets).PrintOut preview:=True

Rob

 

[Dave Peterson]

One way is to just try every number between 0 and 99 to see if you have a
printer named that. I don't and it's really quick to fail.

I'm don't have that kind of printer, so I'm not sure what you'd need for that
second question. Remember to get rid of the Preview:=true when you're done
testing, too.

Option Explicit
Private Sub CancelButton_Click()
Unload Me
End Sub
Private Sub OKButton_Click()
Dim lCtr As Long
Dim iCtr As Long
Dim sCtr As Long
Dim CurPrinter As String
Dim mySheets() As String
ReDim mySheets(1 To Me.ListBox1.ListCount)
Dim FoundIt As Boolean

'save the current printer
CurPrinter = Application.ActivePrinter

'look for something that may match
FoundIt = False
On Error Resume Next
For iCtr = 0 To 99
Application.ActivePrinter = "Adobe PDF on Ne" & Format(iCtr, "00") & ":"
If Err.Number = 0 Then
FoundIt = True
Exit For
Else
'keep looking
Err.Clear
End If
Next iCtr
On Error GoTo 0

If FoundIt = False Then
MsgBox "No PDF printer available"
Beep
Else
sCtr = 0
With Me.ListBox1
For lCtr = 0 To .ListCount - 1
If .Selected(lCtr) Then
sCtr = sCtr + 1
mySheets(sCtr) = .List(lCtr)
End If
Next lCtr
End With

If sCtr = 0 Then
'nothing selected
Beep
Else
Me.Hide
ReDim Preserve mySheets(1 To sCtr)
Worksheets(mySheets).PrintOut preview:=True
'Me.Show
End If
End If

'change back to the previous printer
Application.ActivePrinter = CurPrinter
Unload Me
End Sub
Private Sub UserForm_Initialize()
With Me.ListBox1
.RowSource = ""
.AddItem Sheet3.Name
.AddItem Sheet9.Name
.AddItem Sheet2.Name
.AddItem Sheet10.Name
.MultiSelect = fmMultiSelectMulti
End With
End Sub

=========
Ken Puls has posted this link to his site:
http://www.excelguru.ca/node/21#MultiSingle

That may offer you an alternative.
It uses PdfCreator: http://sourceforge.net/projects/pdfcreator/

 

[RobN]

David,

The addition to the code works good, (at least at home).

The 2nd Q was just me being dumb! Of course I forgot to take out the
preview part.
However, maybe I'm still clueless, as having done that, the code wants to
print the 4 sheets as 2 PDF's. ie it opens the SaveAs dialog box and saves
the first 2 sheets and then the SaveAs dialog appears again and I have to
save the next 2 sheets as another PDF. I can't figure out why.

Furthermore, even if I select only Sheet3 and Sheet2 in the Uerform, the
code prints the 1st 2 sheets listed in the Userform (Sheet3 and Sheet9),
rather than those I've selected.

In summary, I would like it if the sheets I select are the only ones printed
and, only to the one PDF.
I really appreciate your help with this. Thank you!!

Rob

 

[Dave Peterson]

I don't have a PDF driver like this.

But if print to my normal printer, I see one job in the print queue.

Maybe someone can jump in with a suggestion. You may want to share what Adobe
program (version?) that you're using, too.

Or maybe check google or even an Adobe forum if no one chimes in.

Good luck.

David,

The addition to the code works good, (at least at home).

The 2nd Q was just me being dumb! Of course I forgot to take out the
preview part.
However, maybe I'm still clueless, as having done that, the code wants to
print the 4 sheets as 2 PDF's. ie it opens the SaveAs dialog box and saves
the first 2 sheets and then the SaveAs dialog appears again and I have to
save the next 2 sheets as another PDF. I can't figure out why.

Furthermore, even if I select only Sheet3 and Sheet2 in the Uerform, the
code prints the 1st 2 sheets listed in the Userform (Sheet3 and Sheet9),
rather than those I've selected.

In summary, I would like it if the sheets I select are the only ones printed
and, only to the one PDF.
I really appreciate your help with this. Thank you!!

Rob

 

[RobN]

I'm using Adobe Acrobat 6 standard, Office 2007, Windows XP.

BTW Dave, thanks for the extra bits you put in the code, like resetting the
printer, etc.

Rob

 

[Dave Peterson]

Doesn't MS have a download to create pdf files for office 2007?
http://www.microsoft.com/downloads/...11-3E7E-4AE6-B059-A2E79ED87041&displaylang=en

or

http://tinyurl.com/v46jc

(Nope, I've never used it.)

I'm using Adobe Acrobat 6 standard, Office 2007, Windows XP.

BTW Dave, thanks for the extra bits you put in the code, like resetting the
printer, etc.

Rob

 

[RobN]

Yes Dave, I have done that and used it to create PDF files. However, I'm
running the workbook in vs 2003 as others I need to send it to don't have
2007. I believe there is a an addin/patch or something like that which
allows pre 2007 users to open 2007 workbooks, but the same people don't want
to be bothered with that either.

If I can't get any further help, I'll leave to code as you have provided as
that alone is helpful.

Rob

 

[Dave Peterson]

Good luck!

Yes Dave, I have done that and used it to create PDF files. However, I'm
running the workbook in vs 2003 as others I need to send it to don't have
2007. I believe there is a an addin/patch or something like that which
allows pre 2007 users to open 2007 workbooks, but the same people don't want
to be bothered with that either.

If I can't get any further help, I'll leave to code as you have provided as
that alone is helpful.

Rob