Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Print Pages by Page Number
I think this is an easy one for an experienced VBA guy unlike myself (still
learning). I have a Worksheet that is Sorted by the sales persons initials in Column B. I then have horizontal pages breaks inserted so I can seperate each sales persons individual schedule for the day. I designed a UserForm that has a checkbox by each sales persons name. I want the user to be able to check the sales schedules they want to print, hit print, and only the checked schedules would be printed. Thanks in advance! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Print Pages by Page Number
Hi Ryan,
I am assuming that you are only looking for pointers on how to go about this so I'll post some code that I am sure will help. The code actually resets the print areas but you indicated that you wanted to be able to select by the page number so I have included some code at the bottom that should point you in the right direction to modify the other code because the principles are the same. When you open the VBA editor, double click ThisWorkbook and paste it in there. Private Sub Workbook_BeforePrint(Cancel As Boolean) Dim wsName As String Dim myPage As Variant wsName = ActiveSheet.Name 'Case routine ensures that macro only runs with the 'required worksheets and exits sub with other worksheets Select Case wsName Case "MySchedule" Cancel = True 'Cancel initial print call GoTo printMySchedule Case "YourReport" 'Can have separate routines for various worksheets 'Cancel = True 'Cancel initial print call 'GoTo printYourSchedule Case Else 'Handles worksheets that do not require 'special routines so exit and print as normal Exit Sub End Select printMySchedule: 'Following line inserted for testing purposes myPage = "person1" 'Can replace following code with a loop to cover multiple selections Select Case myPage Case "person1" ActiveSheet.PageSetup.PrintArea = "$A$1:$I$59" Case "Person2" ActiveSheet.PageSetup.PrintArea = "$J$1:$R$59" Case Else MsgBox "No valid sales person selected for printing" Exit Sub End Select 'Suppress recursive calls to this sub Application.EnableEvents = False ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True Application.EnableEvents = True End Sub Code to use if selecting by page number to print. Dim myPageNbr As Single myPageNbr = 2 ActiveWindow.SelectedSheets.PrintOut _ From:=myPageNbr, To:=myPageNbr, Copies:=1, Collate:=True Feel free to get back to me if you still have any problems. (It will help if you post the code as far as you have got) Regards, OssieMac "RyanH" wrote: I think this is an easy one for an experienced VBA guy unlike myself (still learning). I have a Worksheet that is Sorted by the sales persons initials in Column B. I then have horizontal pages breaks inserted so I can seperate each sales persons individual schedule for the day. I designed a UserForm that has a checkbox by each sales persons name. I want the user to be able to check the sales schedules they want to print, hit print, and only the checked schedules would be printed. Thanks in advance! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Print Pages by Page Number
Thanks for the Reply Ossie. Unfortunately, I don't think this is what I am
looking for. Basically I have code that sorts Sheets("Global Production Schedule") by each individual sales person which is located in Column "B". Then it inserts a horizontal page break to separate the entire schedule by sales person. I then have a userfrom with a checkbox assigned to each sales person. I want the user to check which sales person schedules she wants to print then click the print button on my userform. I need the code to then find which page number(s) is associated with the sales person(s) she checked and print them. Below is my code I have so far that sorts the schedule, separates the schedules, and set the print area: Sub SalesSchedule() Application.ScreenUpdating = False Sheets("Global Production Schedule").Rows("3:400").Sort _ Key1:=Worksheets("Global Production Schedule").Range("B1"), _ Key2:=Worksheets("Global Production Schedule").Range("K1"), _ Key3:=Worksheets("Global Production Schedule").Range("J1") Sheets("Global Production Schedule").Select Call SetPrintArea Call SetVPageBreak Dim rng As Range Dim i As Integer For Each rng In ActiveSheet.Range("B3:B400") If Not rng.Row = 3 Then If (Not rng.Value = rng.Offset(-1).Value) Then ActiveSheet.HPageBreaks.Add Befo=Range("A" & rng.Row) End If End If Next rng Application.ScreenUpdating = True EndMsg = MsgBox("The Sales Schedule has been produced, would you like to Print?", vbYesNo) If EndMsg < vbYes Then Exit Sub PrintUserForm.Show ***** THIS IS WERE I WANT CODE TO PRINT SCECIFIC SCHEDULES. ***** End Sub Thanks Again!! "OssieMac" wrote: Hi Ryan, I am assuming that you are only looking for pointers on how to go about this so I'll post some code that I am sure will help. The code actually resets the print areas but you indicated that you wanted to be able to select by the page number so I have included some code at the bottom that should point you in the right direction to modify the other code because the principles are the same. When you open the VBA editor, double click ThisWorkbook and paste it in there. Private Sub Workbook_BeforePrint(Cancel As Boolean) Dim wsName As String Dim myPage As Variant wsName = ActiveSheet.Name 'Case routine ensures that macro only runs with the 'required worksheets and exits sub with other worksheets Select Case wsName Case "MySchedule" Cancel = True 'Cancel initial print call GoTo printMySchedule Case "YourReport" 'Can have separate routines for various worksheets 'Cancel = True 'Cancel initial print call 'GoTo printYourSchedule Case Else 'Handles worksheets that do not require 'special routines so exit and print as normal Exit Sub End Select printMySchedule: 'Following line inserted for testing purposes myPage = "person1" 'Can replace following code with a loop to cover multiple selections Select Case myPage Case "person1" ActiveSheet.PageSetup.PrintArea = "$A$1:$I$59" Case "Person2" ActiveSheet.PageSetup.PrintArea = "$J$1:$R$59" Case Else MsgBox "No valid sales person selected for printing" Exit Sub End Select 'Suppress recursive calls to this sub Application.EnableEvents = False ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True Application.EnableEvents = True End Sub Code to use if selecting by page number to print. Dim myPageNbr As Single myPageNbr = 2 ActiveWindow.SelectedSheets.PrintOut _ From:=myPageNbr, To:=myPageNbr, Copies:=1, Collate:=True Feel free to get back to me if you still have any problems. (It will help if you post the code as far as you have got) Regards, OssieMac "RyanH" wrote: I think this is an easy one for an experienced VBA guy unlike myself (still learning). I have a Worksheet that is Sorted by the sales persons initials in Column B. I then have horizontal pages breaks inserted so I can seperate each sales persons individual schedule for the day. I designed a UserForm that has a checkbox by each sales persons name. I want the user to be able to check the sales schedules they want to print, hit print, and only the checked schedules would be printed. Thanks in advance! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Print Pages by Page Number
Hi Ryan,
I can only offer pointers as to how you might achieve the desired result because there is still too much that I can't really picture. I would have thought that you could still incorporate the principles of setting the print area for each sales person by finding the first instance of the sales person in column B and then find the change to another sales person as you have done for the HPageBreaks. Also how do you know how many pages for each sales person? Is there always only one or can there be multiple pages. If multiple pages, is it always the same number of pages for each sales person? I would think it possible that there could be auto page breaks between the hard page breaks. Multiple pages for a sales person would handle itself if you set the print areas individually prior to printing. If you still want to run with the page number to print then I would suggest that you set up a table somewhere in your workbook and when inserting the page breaks you keep a record of the page numbers pertaining to each sales person. You can then use a method of looking up the page numbers associated with the sales person. However, if there are multiple pages for each sales person, then where you insert hard page breaks, you also need to count rows and insert intermediate hard breaks as well which will give you the opportunity to count all pages. The table would then need 2 columns for the page numbers (first and last page). Anyway I hope that I have provided some insight that will help you to come up with something which will meet your needs. Regards, OssieMac |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Print Pages by Page Number
Thanks for sticking with this issue Ossie! Each sales persons schedule can
be 1 to 3 pages long, maybe longer in the future. Is there a Keyword or any syntax that can find the page numbers of the selected sales people and then tell the printer to only print those selected pages? "OssieMac" wrote: Hi Ryan, I can only offer pointers as to how you might achieve the desired result because there is still too much that I can't really picture. I would have thought that you could still incorporate the principles of setting the print area for each sales person by finding the first instance of the sales person in column B and then find the change to another sales person as you have done for the HPageBreaks. Also how do you know how many pages for each sales person? Is there always only one or can there be multiple pages. If multiple pages, is it always the same number of pages for each sales person? I would think it possible that there could be auto page breaks between the hard page breaks. Multiple pages for a sales person would handle itself if you set the print areas individually prior to printing. If you still want to run with the page number to print then I would suggest that you set up a table somewhere in your workbook and when inserting the page breaks you keep a record of the page numbers pertaining to each sales person. You can then use a method of looking up the page numbers associated with the sales person. However, if there are multiple pages for each sales person, then where you insert hard page breaks, you also need to count rows and insert intermediate hard breaks as well which will give you the opportunity to count all pages. The table would then need 2 columns for the page numbers (first and last page). Anyway I hope that I have provided some insight that will help you to come up with something which will meet your needs. Regards, OssieMac |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Print Pages by Page Number
thank you "OssieMac"
just wanted to say thanks for your input here... this solved a problem i have been agonizing over for days. this is how your code ended up on my form: Private Sub Workbook_BeforePrint(Cancel As Boolean) If ActiveSheet.Range("B125") = 0 And ActiveSheet.Range("B166") = 0 Then ActiveWindow.SelectedSheets.PrintOut _ From:="1", To:="3", Copies:=1, Collate:=True ElseIf ActiveSheet.Range("B125") = 0 And ActiveSheet.Range("B166") < 0 Then ActiveWindow.SelectedSheets.PrintOut _ From:="5", To:="5", Copies:=1, Collate:=True ActiveWindow.SelectedSheets.PrintOut _ From:="1", To:="3", Copies:=1, Collate:=True ElseIf ActiveSheet.Range("B125") < 0 And ActiveSheet.Range("B166") = 0 Then ActiveWindow.SelectedSheets.PrintOut _ From:="1", To:="4", Copies:=1, Collate:=True Else: ActiveWindow.SelectedSheets. _ PrintOut From:="1", To:="5", Copies:=1, Collate:=True End If End Sub worked like a charm; i had been trying to set a print area that was not continuous, and Union() would not do it. your solution in this post did the trick. "OssieMac" wrote: Hi Ryan, I am assuming that you are only looking for pointers on how to go about this so I'll post some code that I am sure will help. The code actually resets the print areas but you indicated that you wanted to be able to select by the page number so I have included some code at the bottom that should point you in the right direction to modify the other code because the principles are the same. When you open the VBA editor, double click ThisWorkbook and paste it in there. Private Sub Workbook_BeforePrint(Cancel As Boolean) Dim wsName As String Dim myPage As Variant wsName = ActiveSheet.Name 'Case routine ensures that macro only runs with the 'required worksheets and exits sub with other worksheets Select Case wsName Case "MySchedule" Cancel = True 'Cancel initial print call GoTo printMySchedule Case "YourReport" 'Can have separate routines for various worksheets 'Cancel = True 'Cancel initial print call 'GoTo printYourSchedule Case Else 'Handles worksheets that do not require 'special routines so exit and print as normal Exit Sub End Select printMySchedule: 'Following line inserted for testing purposes myPage = "person1" 'Can replace following code with a loop to cover multiple selections Select Case myPage Case "person1" ActiveSheet.PageSetup.PrintArea = "$A$1:$I$59" Case "Person2" ActiveSheet.PageSetup.PrintArea = "$J$1:$R$59" Case Else MsgBox "No valid sales person selected for printing" Exit Sub End Select 'Suppress recursive calls to this sub Application.EnableEvents = False ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True Application.EnableEvents = True End Sub Code to use if selecting by page number to print. Dim myPageNbr As Single myPageNbr = 2 ActiveWindow.SelectedSheets.PrintOut _ From:=myPageNbr, To:=myPageNbr, Copies:=1, Collate:=True Feel free to get back to me if you still have any problems. (It will help if you post the code as far as you have got) Regards, OssieMac "RyanH" wrote: I think this is an easy one for an experienced VBA guy unlike myself (still learning). I have a Worksheet that is Sorted by the sales persons initials in Column B. I then have horizontal pages breaks inserted so I can seperate each sales persons individual schedule for the day. I designed a UserForm that has a checkbox by each sales persons name. I want the user to be able to check the sales schedules they want to print, hit print, and only the checked schedules would be printed. Thanks in advance! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Number of pages in worksheet doesn't match Print Preview pages | Excel Discussion (Misc queries) | |||
PRINT PAGES FROM DIFF TABS WITH PAGE NUMBER 11 TO 19 | Excel Discussion (Misc queries) | |||
Setting the print area in page set up to print 1 page wide by 2 pages tall | Excel Discussion (Misc queries) | |||
sheet tabs as page number and in a cell page of pages? | Excel Discussion (Misc queries) | |||
Can I print 2 pages on 1 page? | Excel Discussion (Misc queries) |