Page index sheet

ABabeNChrist · Post by **ABabeNChrist** » 28 Sep 2012, 15:04

I have a workbook with 20 different worksheets. All worksheets are originally hidden and desired sheets are selected when starting a new report. I also use 5 other sheets for storing different types of data (that are not printed or indexed). What I’d like to accomplish is creating a page index sheet, so that whatever sheets are visible they will be added to the index sheet in the order of their selection and if possible what page number each worksheet would begin in the order as they are printed, is this possible

Post by **HansV** » 28 Sep 2012, 15:10

How are the sheets being selected?

ABabeNChrist · Post by **ABabeNChrist** » 28 Sep 2012, 15:19

I use a userform to make my selections using this code below

Code: Select all

Private Sub CommandButton1_Click()

    Dim wssheet As Worksheet
    Dim blnSelected As Boolean

    Application.ScreenUpdating = False

    ActiveWorkbook.Protect PassWord:="", Structure:=False, Windows:=False

    If CheckBox37.Value = True Then
        blnSelected = True
        Sheets("Cover Page").Visible = xlSheetVisible
        Sheets("Cover Page").Protect DrawingObjects:=False, Contents:=True, Scenarios:=True, _
                                     PassWord:="", UserInterFaceOnly:=True
    End If

    If CheckBox36.Value = True Then
        blnSelected = True
        Sheets("Client Information").Visible = xlSheetVisible
        Sheets("Client Information").Protect DrawingObjects:=False, Contents:=True, Scenarios:=True, _
                                             PassWord:="", UserInterFaceOnly:=True
    End If

    If CheckBox34.Value = True Then
        blnSelected = True
        Sheets("Utilities").Visible = xlSheetVisible
        Sheets("Utilities").Protect DrawingObjects:=False, Contents:=True, Scenarios:=True, _
                                    PassWord:="", UserInterFaceOnly:=True
    End If

    If CheckBox33.Value = True Then
        blnSelected = True
        Sheets("Grounds").Visible = xlSheetVisible
        Sheets("Grounds").Protect DrawingObjects:=False, Contents:=True, Scenarios:=True, _
                                  PassWord:="", UserInterFaceOnly:=True
    End If

    If CheckBox32.Value = True Then
        blnSelected = True
        Sheets("Structural Systems").Visible = xlSheetVisible
        Sheets("Structural Systems").Protect DrawingObjects:=False, Contents:=True, Scenarios:=True, _
                                                 PassWord:="", UserInterFaceOnly:=True
    End If

    If CheckBox31.Value = True Then
        blnSelected = True
        Sheets("Detached Structure").Visible = xlSheetVisible
        Sheets("Detached Structure").Protect DrawingObjects:=False, Contents:=True, Scenarios:=True, _
                                 PassWord:="", UserInterFaceOnly:=True
    End If

    If CheckBox30.Value = True Then
        blnSelected = True
        Sheets("Roof & Attic").Visible = xlSheetVisible
        Sheets("Roof & Attic").Protect DrawingObjects:=False, Contents:=True, Scenarios:=True, _
                               PassWord:="", UserInterFaceOnly:=True
    End If

    If CheckBox9.Value = True Then
        blnSelected = True
        Sheets("Fireplace & Chimney").Visible = xlSheetVisible
        Sheets("Fireplace & Chimney").Protect DrawingObjects:=False, Contents:=True, Scenarios:=True, _
                                              PassWord:="", UserInterFaceOnly:=True
    End If

    If CheckBox28.Value = True Then
        blnSelected = True
        Sheets("Bathroom(s)").Visible = xlSheetVisible
        Sheets("Bathroom(s)").Protect DrawingObjects:=False, Contents:=True, Scenarios:=True, _
                                      PassWord:="", UserInterFaceOnly:=True
    End If

    If CheckBox26.Value = True Then
        blnSelected = True
        Sheets("Kitchen").Visible = xlSheetVisible
        Sheets("Kitchen").Protect DrawingObjects:=False, Contents:=True, Scenarios:=True, _
                                  PassWord:="", UserInterFaceOnly:=True
    End If

    If CheckBox25.Value = True Then
        blnSelected = True
        Sheets("Kitchen Appliances").Visible = xlSheetVisible
        Sheets("Kitchen Appliances").Protect DrawingObjects:=False, Contents:=True, Scenarios:=True, _
                                             PassWord:="", UserInterFaceOnly:=True
    End If

    If CheckBox21.Value = True Then
        blnSelected = True
        Sheets("Heating and Cooling").Visible = xlSheetVisible
        Sheets("Heating and Cooling").Protect DrawingObjects:=False, Contents:=True, Scenarios:=True, _
                                              PassWord:="", UserInterFaceOnly:=True
    End If

    If CheckBox22.Value = True Then
        blnSelected = True
        Sheets("Water Heater").Visible = xlSheetVisible
        Sheets("Water Heater").Protect DrawingObjects:=False, Contents:=True, Scenarios:=True, _
                                       PassWord:="", UserInterFaceOnly:=True
    End If

    If CheckBox23.Value = True Then
        blnSelected = True
        Sheets("Pool Spa").Visible = xlSheetVisible
        Sheets("Pool Spa").Protect DrawingObjects:=False, Contents:=True, Scenarios:=True, _
                                   PassWord:="", UserInterFaceOnly:=True
    End If

    If CheckBox35.Value = True Then
        blnSelected = True
        Sheets("Summary").Visible = xlSheetVisible
        Sheets("Summary").Protect DrawingObjects:=False, Contents:=True, Scenarios:=True, _
                                  PassWord:="", UserInterFaceOnly:=True
    End If

    If CheckBox24.Value = True Then
        blnSelected = True
        Sheets("Additional Photos").Visible = xlSheetVisible
        Sheets("Additional Photos").Protect DrawingObjects:=False, Contents:=True, Scenarios:=True, _
                                            PassWord:="", UserInterFaceOnly:=True
    End If

    Application.ScreenUpdating = True

    If blnSelected = True Then
        Worksheets("_").Visible = xlSheetHidden
    End If

    For Each wssheet In ActiveWorkbook.Worksheets
        If wssheet.Visible = xlSheetVisible Then
            wssheet.Select
            Exit For
        End If
    Next wssheet
    ActiveWorkbook.Protect PassWord:="", Structure:=True, Windows:=True

    If blnSelected = True Then
        MsgBox "You may now begin working on your report"
        Unload Me
        Unload UserForm3
    Else
        MsgBox "No check boxes were selected"
    End If
End Sub

Post by **HansV** » 28 Sep 2012, 15:38

There is no way to determine in which order the check boxes were ticked from this code.

ABabeNChrist · Post by **ABabeNChrist** » 28 Sep 2012, 15:48

They are in the correct order. One other thing to note is sometimes when making my selection of desired sheets is that I could miss a selected sheet and then have to return to add missed sheet

Post by **HansV** » 28 Sep 2012, 15:54

What do you mean by "they are in the correct order"? I thought that you wanted to list the sheets in the order they were selected by the user. Am I wrong?

ABabeNChrist · Post by **ABabeNChrist** » 28 Sep 2012, 17:52

HansV wrote:What do you mean by "they are in the correct order"? I thought that you wanted to list the sheets in the order they were selected by the user. Am I wrong?

I’m sorry, what I was referring to was the order that the sheets are when visible (tabs along bottom)

Post by **HansV** » 28 Sep 2012, 19:31

Create a sheet named Index in your workbook.
Use this code as starting point:

Code: Select all

Sub ListSheets()
    Dim wsh As Worksheet
    Dim wshI As Worksheet
    Dim lngPage As Long
    Dim lngRow As Long
    Set wshI = Worksheets("Index")
    lngPage = 1
    lngRow = 1
    wshI.Cells.ClearContents
    For Each wsh In Worksheets
        If wsh.Visible = xlSheetVisible And wsh.Name <> "Index" Then
            wshI.Range("A" & lngRow) = wsh.Name
            wshI.Range("B" & lngRow) = lngPage
            lngRow = lngRow + 1
            wsh.Activate
            lngPage = lngPage + Application.ExecuteExcel4Macro("Get.Document(50)")
        End If
    Next wsh
End Sub

ABabeNChrist · Post by **ABabeNChrist** » 28 Sep 2012, 19:58

Thank Hans
I have 5 different sheets that are used for data that may or may not be visible and I do not wish to have added to index. How do I prevent those from being added

Post by **HansV** » 28 Sep 2012, 20:12

You can use a Select Case...End Select block to exclude them.

ABabeNChrist · Post by **ABabeNChrist** » 28 Sep 2012, 23:42

HansV wrote:You can use a Select Case...End Select block to exclude them.

I tried and I can’t seem to find the correct way to use Select Case

Post by **HansV** » 29 Sep 2012, 02:05

Like this

Code: Select all

Sub ListSheets()
    Dim wsh As Worksheet
    Dim wshI As Worksheet
    Dim lngPage As Long
    Dim lngRow As Long
    Set wshI = Worksheets("Index")
    lngPage = 1
    lngRow = 1
    wshI.Cells.ClearContents
    For Each wsh In Worksheets
        If wsh.Visible = xlSheetVisible Then
            Select Case wsh.Name
                Case "Index", "Sheet to skip", "Other sheet to skip", _
                    "Yet another sheet to skip"
                    ' Do nothing
                Case Else
                    wshI.Range("A" & lngRow) = wsh.Name
                    wshI.Range("B" & lngRow) = lngPage
                    lngRow = lngRow + 1
                    wsh.Activate
                    lngPage = lngPage + Application.ExecuteExcel4Macro("Get.Document(50)")
            End Select
        End If
    Next wsh
End Sub

ABabeNChrist · Post by **ABabeNChrist** » 29 Sep 2012, 04:56

Thank you Hans, works great

ABabeNChrist · Post by **ABabeNChrist** » 19 Mar 2013, 17:03

After using this a few times I noticed that there are times when a selected sheet is visible but is not selected (using checkbox) and is still add to index sheet.

Code: Select all

    If CheckBox1.Value And Sheets("Cover Page").Visible = xlSheetVisible Then
        Sheets("Cover Page").Select Replace:=True
        blnSelected = True
    End If

Can the blnSelected = True also be added as part of the index selection

Post by **HansV** » 19 Mar 2013, 17:17

Sorry, I don't understand your question.

ABabeNChrist · Post by **ABabeNChrist** » 19 Mar 2013, 17:57

There are times when a sheet that may not be listed within the code (post #82877)

Code: Select all

Case "Index", "Sheet to skip", "Other sheet to skip", _
"Yet another sheet to skip"
' Do nothing

and while some other sheets may still be visible I may not wish to have them added to Index sheet. I would like only selected sheets to be added to Index sheet through checkbox selection.
Here is the current code I use

Code: Select all

Private Sub CommandButton4_Click()
    Dim blnSelected As Boolean
    Dim wsh As Worksheet
    Dim wshI As Worksheet
    Dim lngPage As Long
    Dim lngRow As Long

    ActiveWorkbook.Protect PassWord:="", Structure:=False, Windows:=False

    Application.ScreenUpdating = False

    Sheets("Index").Visible = xlSheetVisible
    Sheets("Inspection Agreement").Visible = xlSheetVisible

    Set wshI = Worksheets("Index")
    lngPage = 1
    lngRow = 6
    wshI.Cells.ClearContents
    For Each wsh In Worksheets
        If wsh.Visible = xlSheetVisible Then
            Select Case wsh.Name
            Case "Email", "_", "Invoice", "Report Information Log", "Realtors", _
                 "Format Comment", "Color Setting", "Inspection Log"
                ' Do nothing
            Case Else
                wshI.Range("B" & lngRow) = wsh.Name
                wshI.Range("C" & lngRow) = lngPage
                lngRow = lngRow + 1
                wsh.Activate
                lngPage = lngPage + Application.ExecuteExcel4Macro("Get.Document(50)")
            End Select
        End If
    Next wsh
    Sheets("Index").Range("B3").Value = " Report Index "
    Sheets("Index").Range("B5").Value = " Inspected locations "
    Sheets("Index").Range("C5").Value = " Page # "



    If CheckBox1.Value And Sheets("Cover Page").Visible = xlSheetVisible Then
        Sheets("Cover Page").Select Replace:=True
        blnSelected = True
    End If
    If CheckBox2.Value And Sheets("Client Information").Visible = xlSheetVisible Then
        Sheets("Client Information").Select Replace:=False
        blnSelected = True
    End If
    If CheckBox3.Value And Sheets("Utilities").Visible = xlSheetVisible Then
        Sheets("Utilities").Select Replace:=False
        blnSelected = True
    End If
    If CheckBox4.Value And Sheets("Grounds").Visible = xlSheetVisible Then
        Sheets("Grounds").Select Replace:=False
        blnSelected = True
    End If
    If CheckBox5.Value And Sheets("Structural Systems").Visible = xlSheetVisible Then
        Sheets("Structural Systems").Select Replace:=False
        blnSelected = True
    End If
    If CheckBox6.Value And Sheets("Detached Structure").Visible = xlSheetVisible Then
        Sheets("Detached Structure").Select Replace:=False
        blnSelected = True
    End If
    If CheckBox7.Value And Sheets("Roof & Attic").Visible = xlSheetVisible Then
        Sheets("Roof & Attic").Select Replace:=False
        blnSelected = True
    End If
    If CheckBox8.Value And Sheets("Fireplace & Chimney").Visible = xlSheetVisible Then
        Sheets("Fireplace & Chimney").Select Replace:=False
        blnSelected = True
    End If
    If CheckBox10.Value And Sheets("Bathroom(s)").Visible = xlSheetVisible Then
        Sheets("Bathroom(s)").Select Replace:=False
        blnSelected = True
    End If
    If CheckBox12.Value And Sheets("Kitchen").Visible = xlSheetVisible Then
        Sheets("Kitchen").Select Replace:=False
        blnSelected = True
    End If
    If CheckBox13.Value And Sheets("Kitchen Appliances").Visible = xlSheetVisible Then
        Sheets("Kitchen Appliances").Select Replace:=False
        blnSelected = True
    End If
    If CheckBox14.Value And Sheets("Heating and Cooling").Visible = xlSheetVisible Then
        Sheets("Heating and Cooling").Select Replace:=False
        blnSelected = True
    End If
    If CheckBox15.Value And Sheets("Water Heater").Visible = xlSheetVisible Then
        Sheets("Water Heater").Select Replace:=False
        blnSelected = True
    End If
    If CheckBox16.Value And Sheets("Pool Spa").Visible = xlSheetVisible Then
        Sheets("Pool Spa").Select Replace:=False
        blnSelected = True
    End If
    If CheckBox17.Value And Sheets("Summary").Visible = xlSheetVisible Then
        Sheets("Summary").Select Replace:=False
        blnSelected = True
    End If
    If CheckBox18.Value And Sheets("Additional Photos").Visible = xlSheetVisible Then
        Sheets("Additional Photos").Select Replace:=False
        blnSelected = True
    End If
    If CheckBox19.Value Then
        Sheets("Index").Select Replace:=False
        blnSelected = True
    End If
    If CheckBox20.Value Then
        Sheets("Inspection Agreement").Select Replace:=False
        blnSelected = True
    End If

    If blnSelected = True Then

        ActiveWindow.SelectedSheets.Application.Dialogs(xlDialogPrint).Show
        Unload Me
        Unload UserForm4
        Sheets("Index").Visible = xlSheetHidden
        Sheets("Inspection Agreement").Visible = xlSheetHidden
        ActiveWorkbook.Protect PassWord:="", Structure:=True, Windows:=True

    Else
        MsgBox "No check boxes were selected"

    End If
    Application.ScreenUpdating = True
    ActiveSheet.Select

End Sub

Post by **HansV** » 19 Mar 2013, 20:32

I have no idea what you mean. Could you try to explain it more clearly? Thanks!

ABabeNChrist · Post by **ABabeNChrist** » 19 Mar 2013, 23:25

I apologize; I’ll try to explain a little more clearly. I have a workbook with approximately 27 worksheets, 20 of which are used to perform an inspection and the other 7 sheets are used to store data and or additional forms ("_", "Invoice", "Report Information Log", "Realtors", "Format Comment", "Color Setting", "Inspection Log”). When the workbook is first opened all worksheets are hidden except “_” sheet. Next using a userform with checkboxes I would only select desired sheets needed between the possible 20. Once I make my selection worksheet “_” hides, while selected sheet are unhidden. When my report is completed and I’m ready to print I would also use a userform with checkboxes to select which worksheets I wish to print. There are times when an unhidden sheet would not be print, but since it is unhidden it is added to the index list. I would like only the selected or printed sheet to be reflected on the index sheet.

Post by **HansV** » 19 Mar 2013, 23:56

I notice that the check boxes are named CheckBox1 ... CheckBox8, CheckBox10, CheckBox12 ... CheckBox20.
Please rename CheckBox10 to CheckBox 9, CheckBox12 to Checkbox10, CheckBox13 to CheckBox11, etc. and finally Checkbox20 to Checkbox18, so that they are numbered consecutively.

You can then use

Code: Select all

Private Sub CommandButton4_Click()
    Dim blnSelected As Boolean
    Dim wshI As Worksheet
    Dim lngPage As Long
    Dim lngRow As Long
    Dim i As Long
    Dim arrSheets As Variant

    arrSheets = Array("Cover Page", "Client Information", "Utilities", "Grounds", "Structural Systems", _
        "Detached Structure", "Roof & Attic", "Fireplace & Chimney", "Bathroom(s)", "Kitchen", _
        "Kitchen Appliances", "Heating and Cooling", "Water Heater", "Pool Spa", "Summary", _
        "Additional Photos", "Index", "Inspection Agreement")

    ActiveWorkbook.Protect Password:="", Structure:=False, Windows:=False

    Application.ScreenUpdating = False

    Sheets("Index").Visible = xlSheetVisible
    Sheets("Inspection Agreement").Visible = xlSheetVisible

    Set wshI = Worksheets("Index")
    lngPage = 1
    lngRow = 6
    wshI.Cells.ClearContents
    Sheets("Index").Range("B3").Value = " Report Index "
    Sheets("Index").Range("B5").Value = " Inspected locations "
    Sheets("Index").Range("C5").Value = " Page # "

    For i = 1 To 18
        If Me.Controls("CheckBox" & i).Value And Sheets(arrSheets(i - 1)).Visible = xlSheetVisible Then
            wshI.Range("B" & lngRow) = arrSheets(i - 1)
            wshI.Range("C" & lngRow) = lngPage
            lngRow = lngRow + 1
            Sheets(arrSheets(i - 1)).Activate
            lngPage = lngPage + Application.ExecuteExcel4Macro("Get.Document(50)")
        End If
    Next i

    For i = 1 To 18
        If Me.Controls("CheckBox" & i).Value And Sheets(arrSheets(i - 1)).Visible = xlSheetVisible Then
            Sheets(arrSheets(i - 1)).Select Replace:=True
            blnSelected = True
        End If
    Next i

    If blnSelected = True Then
        ActiveWindow.SelectedSheets.Application.Dialogs(xlDialogPrint).Show
        Unload Me
        Unload UserForm4
        Sheets("Index").Visible = xlSheetHidden
        Sheets("Inspection Agreement").Visible = xlSheetHidden
        ActiveWorkbook.Protect Password:="", Structure:=True, Windows:=True
    Else
        MsgBox "No check boxes were selected"
    End If
    Application.ScreenUpdating = True
    ActiveSheet.Select
End Sub

ABabeNChrist · Post by **ABabeNChrist** » 20 Mar 2013, 01:46

I made the changes as you suggested and replaced code, but it only seems to print the last sheet?
Not sure why

Eileen's Lounge

Page index sheet

Page index sheet

Re: Page index sheet

Re: Page index sheet

Re: Page index sheet

Re: Page index sheet

Re: Page index sheet

Re: Page index sheet

Re: Page index sheet

Re: Page index sheet

Re: Page index sheet

Re: Page index sheet

Re: Page index sheet

Re: Page index sheet

Re: Page index sheet

Re: Page index sheet

Re: Page index sheet

Re: Page index sheet

Re: Page index sheet

Re: Page index sheet

Re: Page index sheet