Referencing selected tabs to print selection & update table of contents sheet

ML!

New Member
Joined
Nov 11, 2009
Messages
35
Hi all...I searched but didn't find exactly what I needed. I have a workbook with 31 sheets. It is a price guide with each category on a separate worksheet. I figured out how to list the sheets on a separate tab. What my client wants is the ability to:

1. select certain categories for printing, the ToC, Cover and backcover pages have to print in every case

2. the ToC has to change depending on the sheets selected.

I'd rather write some code and give him an an easy command button rather then teaching him how to select non-concurrent sheets and printing only active sheets.

What I'm really stuck on is the updating of the ToC with active sheets only (category and starting page which changes depending on pages selected).

Thanks in advance for any brilliant ideas!

ML
 

Some videos you may like

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

Steeviee

Active Member
Joined
Sep 9, 2009
Messages
380
Hi all...I searched but didn't find exactly what I needed. I have a workbook with 31 sheets. It is a price guide with each category on a separate worksheet. I figured out how to list the sheets on a separate tab. What my client wants is the ability to:

1. select certain categories for printing, the ToC, Cover and backcover pages have to print in every case

2. the ToC has to change depending on the sheets selected.

I'd rather write some code and give him an an easy command button rather then teaching him how to select non-concurrent sheets and printing only active sheets.

What I'm really stuck on is the updating of the ToC with active sheets only (category and starting page which changes depending on pages selected).

Thanks in advance for any brilliant ideas!

ML

I would suggest that you give him a print button that brings up a user form - he can then check each page that he wants. You can then have in the code of the userform that when he presses OK, it unloads the userform and hides all the sheets that have not been checked (eg. have variable1=0 or 1 as the change event for option1 etc, if variable1 = 0 hide that page) and then print the whole workbook (sheets that are hidden do not print); at the end of the code unhide all of the sheets.

I hope that makes sense.
 

Zack Barresse

MrExcel MVP
Joined
Dec 9, 2003
Messages
10,881
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
  3. Web
Printing a desired number of sheets is easy. But what exactly changes on the TOC? Not sure what you mean there. For printing the sheets, how about a pop up dialog box, let's the user check a series of checkboxes for which sheets they want to print? We can automatically print any number of other sheets (i.e. TOC) every time as well.
 

ML!

New Member
Joined
Nov 11, 2009
Messages
35
Thanks guys!

A combination of these ideas on printing the selected sheets sound great...I may need to come back for some support on implementation.

Zack, the ToC needs to update automatically to only show the printing categories and their new page numbers. Page numbering has been setup as Auto in the page setup and page number footers are on all the data sheets (not cover/ToC/back cover). So if only Categories 3,5 and 6 are printed, Category 3 is on page 1 and only 3, 5 and 6 should show on the ToC.

TIA
 

Zack Barresse

MrExcel MVP
Joined
Dec 9, 2003
Messages
10,881
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
  3. Web

ADVERTISEMENT

Ok, so now we'll need to know how your TOC is setup. Can you perhaps give us a detailed description of what you have, and along with an example of what you're talking about along with expected results (of a specific example)? A picture would help as well. :)
 

ML!

New Member
Joined
Nov 11, 2009
Messages
35
It really isn't 'set up'. For the first version, it was just created manually with text as below (category and line in one cell, page range in next)

Category 1..............1-2
Category 2..............3
Category 3..............4-6

I don't need the range but the first page of the category would be helpful.

Attachments aren't allowed on this board. What's the best way to give you a pic? I guess I can upload it somewhere...

There's no urgency on this Zack and I know how busy you are so please address at your leisure :)
 

Zack Barresse

MrExcel MVP
Joined
Dec 9, 2003
Messages
10,881
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
  3. Web

ADVERTISEMENT

Well, I'm not sure how your names of your worksheets are setup, i.e. how they're named. You might want to take a look at the code and change them accordingly. Also it assumes the structure of your TOC sheet, with the data starting in A5 and going down, so your header can be anywhere from row 1 to 4. Anyway, I think this does what you're asking for....




<font face=Courier New><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN><br><br><SPAN style="color:#00007F">Sub</SPAN> PrintMySheets()<br><br><SPAN style="color:#007F00">'#######################################################################################</SPAN><br><SPAN style="color:#007F00">'Original code from J-Walk's page, found here:</SPAN><br><SPAN style="color:#007F00">'http://spreadsheetpage.com/index.php/tip/displaying_a_menu_of_worksheets_to_print/</SPAN><br><SPAN style="color:#007F00">'#######################################################################################</SPAN><br><br>    <SPAN style="color:#00007F">Dim</SPAN> wb <SPAN style="color:#00007F">As</SPAN> Workbook, wsTOC <SPAN style="color:#00007F">As</SPAN> Worksheet<br>    <SPAN style="color:#00007F">Dim</SPAN> wsCover <SPAN style="color:#00007F">As</SPAN> Worksheet, wsBackCover <SPAN style="color:#00007F">As</SPAN> Worksheet<br>    <SPAN style="color:#00007F">Dim</SPAN> aPages() <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN>, aSheets() <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN>, iCnt <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> iPages <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, iTotalPages <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>, TopPos <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>, SheetCount <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> PrintDlg <SPAN style="color:#00007F">As</SPAN> DialogSheet, CurrentSheet <SPAN style="color:#00007F">As</SPAN> Worksheet, cb <SPAN style="color:#00007F">As</SPAN> CheckBox<br><br>    <SPAN style="color:#00007F">Set</SPAN> wb = ThisWorkbook<br><br>    <SPAN style="color:#007F00">'Check for protected workbook</SPAN><br>    <SPAN style="color:#00007F">If</SPAN> wb.ProtectStructure <SPAN style="color:#00007F">Then</SPAN><br>        MsgBox "Workbook is protected.", vbCritical<br>        <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><br>    <SPAN style="color:#007F00">'Turn off application-level properties/events</SPAN><br>    <SPAN style="color:#00007F">Call</SPAN> TOGGLEEVENTS(False)<br><br>    <SPAN style="color:#007F00">'Set worksheet variables</SPAN><br>    <SPAN style="color:#007F00">'############################################</SPAN><br>    <SPAN style="color:#007F00">'Adjust names as necessary here</SPAN><br>    <SPAN style="color:#00007F">Set</SPAN> wsTOC = wb.Worksheets("TOC")<br>    <SPAN style="color:#00007F">Set</SPAN> wsCover = wb.Worksheets("Cover")<br>    <SPAN style="color:#00007F">Set</SPAN> wsBackCover = wb.Worksheets("Back Cover")<br>    <SPAN style="color:#007F00">'############################################</SPAN><br><br>    <SPAN style="color:#007F00">'Add a temporary dialog sheet</SPAN><br>    <SPAN style="color:#00007F">Set</SPAN> CurrentSheet = ActiveSheet<br>    <SPAN style="color:#00007F">Set</SPAN> PrintDlg = wb.DialogSheets.Add<br><br>    SheetCount = 0<br><br>    <SPAN style="color:#007F00">'Add the checkboxes</SPAN><br><br>    TopPos = 40<br>    <SPAN style="color:#00007F">For</SPAN> i = 1 <SPAN style="color:#00007F">To</SPAN> wb.Worksheets.Count<br>        <SPAN style="color:#00007F">Set</SPAN> CurrentSheet = wb.Worksheets(i)<br>        <SPAN style="color:#007F00">'Skip empty sheets and hidden sheets</SPAN><br>        <SPAN style="color:#00007F">If</SPAN> Application.CountA(CurrentSheet.Cells) <> 0 And CurrentSheet.Visible <SPAN style="color:#00007F">Then</SPAN><br>            <SPAN style="color:#00007F">Select</SPAN> <SPAN style="color:#00007F">Case</SPAN> CurrentSheet.Name<br>            <SPAN style="color:#00007F">Case</SPAN> wsTOC.Name, wsCover.Name, wsBackCover.Name<br>            <SPAN style="color:#00007F">Case</SPAN> <SPAN style="color:#00007F">Else</SPAN><br>                SheetCount = SheetCount + 1<br>                <br>                <SPAN style="color:#007F00">'Set pages in array, with page breaks, horizontal and vertical</SPAN><br>                <SPAN style="color:#00007F">ReDim</SPAN> <SPAN style="color:#00007F">Preserve</SPAN> aPages(1 <SPAN style="color:#00007F">To</SPAN> SheetCount)<br>                aPages(SheetCount) = CurrentSheet.VPageBreaks.Count + 1 & "*" & CurrentSheet.HPageBreaks.Count + 1<br>                <br>                <SPAN style="color:#007F00">'Add checkbox to dialog frame</SPAN><br>                PrintDlg.CheckBoxes.Add 78, TopPos, 150, 16.5<br>                PrintDlg.CheckBoxes(SheetCount).Text = CurrentSheet.Name<br>                TopPos = TopPos + 13<br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Select</SPAN><br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">Next</SPAN> i<br><br>    <SPAN style="color:#007F00">'Move the OK and Cancel buttons</SPAN><br>    PrintDlg.Buttons.Left = 240<br><br>    <SPAN style="color:#007F00">'Set dialog height, width, and caption</SPAN><br>    <SPAN style="color:#00007F">With</SPAN> PrintDlg.DialogFrame<br>        .Height = Application.Max(68, PrintDlg.DialogFrame.Top + TopPos - 34)<br>        .Width = 230<br>        .Caption = "Select sheets to print"<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><br>    <SPAN style="color:#007F00">'Change tab order of OK and Cancel buttons</SPAN><br>    <SPAN style="color:#007F00">'so the 1st option button will have the focus</SPAN><br>    PrintDlg.Buttons("Button 2").BringToFront<br>    PrintDlg.Buttons("Button 3").BringToFront<br><br>    <SPAN style="color:#007F00">'Display the dialog box</SPAN><br>    CurrentSheet.Activate<br>    <br>    <SPAN style="color:#00007F">If</SPAN> SheetCount <> 0 <SPAN style="color:#00007F">Then</SPAN><br>        <SPAN style="color:#00007F">If</SPAN> PrintDlg.Show <SPAN style="color:#00007F">Then</SPAN><br>        <br>            <SPAN style="color:#007F00">'Set TOC and print main sheets</SPAN><br>            iCnt = 1<br>            i = 1<br>            iTotalPages = 3<br>            wsTOC.Range("A5:A" & wsTOC.Rows.Count).ClearContents<br>            <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> cb <SPAN style="color:#00007F">In</SPAN> PrintDlg.CheckBoxes<br>                <SPAN style="color:#00007F">If</SPAN> cb.Value = xlOn <SPAN style="color:#00007F">Then</SPAN><br>                    iPages = Application.Evaluate(aPages(i))<br>                    iTotalPages = iTotalPages + iPages<br>                    wsTOC.Cells(iCnt + 4, 1).Value = cb.Caption & " ... page(s) " & iTotalPages - iPages + 1 & "-" & iTotalPages <SPAN style="color:#007F00">'Application.Evaluate(aPages(i)) & "-" & iPgCnt</SPAN><br>                    iCnt = iCnt + 1<br>                <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>                i = i + 1<br>            <SPAN style="color:#00007F">Next</SPAN> cb<br>            <br>            <SPAN style="color:#007F00">'Print checked sheets</SPAN><br>            wsCover.PrintOut<br>            <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> cb <SPAN style="color:#00007F">In</SPAN> PrintDlg.CheckBoxes<br>                <SPAN style="color:#00007F">If</SPAN> cb.Value = xlOn <SPAN style="color:#00007F">Then</SPAN><br>                    wb.Worksheets(cb.Caption).Activate<br>                    ActiveSheet.PrintOut<br>                <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>            <SPAN style="color:#00007F">Next</SPAN> cb<br>            wsBackCover.PrintOut<br>            <br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">Else</SPAN><br>        MsgBox "All worksheets are empty."<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><br>    <SPAN style="color:#007F00">'Delete temporary dialog sheet</SPAN><br>    PrintDlg.Delete<br><br>    <SPAN style="color:#007F00">'Reactivate Table of Contents</SPAN><br>    wsTOC.Activate<br><br>    <SPAN style="color:#007F00">'Re-activate application-level properties/events</SPAN><br>    <SPAN style="color:#00007F">Call</SPAN> TOGGLEEVENTS(True)<br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br><SPAN style="color:#00007F">Public</SPAN> <SPAN style="color:#00007F">Sub</SPAN> TOGGLEEVENTS(blnState <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN>)<br><SPAN style="color:#007F00">'Originally written by Zack Barresse</SPAN><br>    <SPAN style="color:#00007F">With</SPAN> Application<br>        .DisplayAlerts = blnState<br>        .EnableEvents = blnState<br>        .ScreenUpdating = blnState<br>        <SPAN style="color:#00007F">If</SPAN> blnState <SPAN style="color:#00007F">Then</SPAN> .CutCopyMode = <SPAN style="color:#00007F">False</SPAN><br>        <SPAN style="color:#00007F">If</SPAN> blnState <SPAN style="color:#00007F">Then</SPAN> .StatusBar = <SPAN style="color:#00007F">False</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>




Let us know how it goes. Tested ok for me.
 

ML!

New Member
Joined
Nov 11, 2009
Messages
35
Has anyone told you this lately Zack?

YOU ROCK! I owe you at least a beer. (y)

J-Walk is awesome too but you found the code and made it even better. I also implemented a modified version of Aaron Blood's suggestion (from the same site) to allow the selected sheets to print concurrently and number correctly.

I still have a bit of tweaking to do but you've saved me tons of time (and likely a whack of frustration). I'm very grateful.

ML
 

Zack Barresse

MrExcel MVP
Joined
Dec 9, 2003
Messages
10,881
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
  3. Web
Also, to get the sheet name in one cell and the page numbers in another, look down in the code and replace these lines ....


<font face=Courier New>wsTOC.Cells(iCnt + 4, 1).Value = cb.Caption & " ... page(s) " & iTotalPages - iPages + 1 & "-" & iTotalPages</FONT>


.... with these lines....


<font face=Courier New>wsTOC.Cells(iCnt + 4, 1).Value = cb.Caption<br>wsTOC.Cells(iCnt + 4, 1).NumberFormat = "@*."<br>wsTOC.Cells(iCnt + 4, 2).Value = "'" & iTotalPages - iPages + 1 & "-" & iTotalPages</FONT>


As you can see, it still assumes column A for the sheet name, column B (of the same row) for the page numbers.

Glad it works for you. And thanks. :)
 

Watch MrExcel Video

Forum statistics

Threads
1,122,837
Messages
5,598,379
Members
414,234
Latest member
grlevesq

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top