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

ML! · Nov 30, 2009

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

Steeviee · Nov 30, 2009

ML! said:
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 · Nov 30, 2009

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! · Nov 30, 2009

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 · Nov 30, 2009

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! · Nov 30, 2009

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 · Dec 1, 2009

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....

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

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

ML! · Dec 2, 2009

Has anyone told you this lately Zack?

YOU ROCK! I owe you at least a beer.

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 · Dec 2, 2009

Also, to get the sheet name in one cell and the page numbers in another, look down in the code and replace these lines ....

wsTOC.Cells(iCnt + 4, 1).Value = cb.Caption & " ... page(s) " & iTotalPages - iPages + 1 & "-" & iTotalPages

.... with these lines....

wsTOC.Cells(iCnt + 4, 1).Value = cb.Caption wsTOC.Cells(iCnt + 4, 1).NumberFormat = "@*." wsTOC.Cells(iCnt + 4, 2).Value = "'" & iTotalPages - iPages + 1 & "-" & iTotalPages

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.

ML! · Dec 2, 2009

Works perfectly! You're the awesomest!
ML

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

ML!

New Member

Excel Facts

Steeviee

Active Member

Zack Barresse

MrExcel MVP

ML!

New Member

Zack Barresse

MrExcel MVP

ML!

New Member

Zack Barresse

MrExcel MVP

ML!

New Member

Zack Barresse

MrExcel MVP

ML!

New Member

Similar threads

Forum statistics

Share this page

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

New Member

Excel Facts

Active Member

MrExcel MVP

New Member

MrExcel MVP

New Member

MrExcel MVP

New Member

MrExcel MVP

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock