Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Print a selected number of sheets
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Print a selected number of sheets
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 wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Print a selected number of sheets
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" wrote in message ... 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 wrote: 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Print a selected number of sheets
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 wrote: 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" wrote in message ... 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 wrote: 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 -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Print a selected number of sheets
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" wrote in message ... 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 wrote: 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" wrote in message ... 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 wrote: 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 -- Dave Peterson |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Print a selected number of sheets
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. RobN wrote: 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" wrote in message ... 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 wrote: 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" wrote in message ... 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 wrote: 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 -- Dave Peterson -- Dave Peterson |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Print a selected number of sheets
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" wrote in message ... 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. RobN wrote: 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" wrote in message ... 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 wrote: 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" wrote in message ... 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 wrote: 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 -- Dave Peterson -- Dave Peterson |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Print a selected number of sheets
Doesn't MS have a download to create pdf files for office 2007?
http://www.microsoft.com/downloads/d...displaylang=en or http://tinyurl.com/v46jc (Nope, I've never used it.) RobN wrote: 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" wrote in message ... 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. RobN wrote: 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" wrote in message ... 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 wrote: 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" wrote in message ... 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 wrote: 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 -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Print a selected number of sheets
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" wrote in message ... Doesn't MS have a download to create pdf files for office 2007? http://www.microsoft.com/downloads/d...displaylang=en or http://tinyurl.com/v46jc (Nope, I've never used it.) RobN wrote: 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" wrote in message ... 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. RobN wrote: 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" wrote in message ... 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 wrote: 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" wrote in message ... 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 wrote: 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 -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Print a selected number of sheets
Good luck!
RobN wrote: 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" wrote in message ... Doesn't MS have a download to create pdf files for office 2007? http://www.microsoft.com/downloads/d...displaylang=en or http://tinyurl.com/v46jc (Nope, I've never used it.) RobN wrote: 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" wrote in message ... 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. RobN wrote: 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" wrote in message ... 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 wrote: 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" wrote in message ... 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 wrote: 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 -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Print preview and print only shows 2/3 of page selected | Excel Discussion (Misc queries) | |||
How do I print a workbook in but only print selected worksheets? | Excel Discussion (Misc queries) | |||
Printing selected sheets. | Excel Worksheet Functions | |||
How to print a selected number of sheets? | Excel Worksheet Functions | |||
How to repeat a code for selected sheets (or a contiguous range of sheets) in a Workbook? | Excel Worksheet Functions |