Page index sheet

[HansV]

Does this do what you want?

Code: Select all

Private Sub CommandButton5_Click()
'Microsoft PDF
    Dim blnSelected As Boolean
    Dim wshI As Worksheet
    Dim lngPage As Long
    Dim lngRow As Long
    Dim i As Long
    Dim arrSheets As Variant
    Dim arrNames As Variant

    'The order of array reflects order of index page
    arrSheets = Array("Cover Page", "Index", "Client Information", "Summary", "Utilities", "Grounds", "Structural Systems", _
                      "Detached Structure", "Roof & Attic", "Fireplace & Chimney", "Interior", "Bedroom", "Laundry", "Bathroom(s)", "Kitchen", _
                      "Kitchen Appliances", "Heating and Cooling", "Water Heater", "Pool Spa", "Additional Photos", "Informational")
    arrNames = Array("Cover Page", "Index", "Client Information", "Summary", "Utilities", "Exterior Surfaces", "Interior Surfaces", _
                      "Detached Structure", "Roof & Attic", "Fireplace & Chimney", "Interior", "Bedroom", "Laundry", "Bathroom(s)", "Kitchen", _
                      "Kitchen Appliances", "Heating and Cooling", "Water Heater", "Pool Spa", "Additional Photos", "Laboratory Results")

    ActiveWorkbook.Protect Password:="benji", Structure:=False, Windows:=False
    Sheets("Index").Unprotect ""

    Application.ScreenUpdating = False

    If CheckBox2.Value = True Then
        Sheets("Index").Visible = xlSheetVisible
    Else
        Sheets("Index").Visible = xlSheetHidden
    End If

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

    For i = 1 To 21
        If Me.Controls("CheckBox" & i).Value And Sheets(arrSheets(i - 1)).Visible = xlSheetVisible Then
            wshI.Range("B" & lngRow) = arrNames(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 21
        If Me.Controls("CheckBox" & i).Value And Sheets(arrSheets(i - 1)).Visible = xlSheetVisible Then
            Sheets(arrSheets(i - 1)).Select Replace:=Not blnSelected
            blnSelected = True
        End If
    Next i

    If blnSelected = True Then
        Dim WSHShell As Object
        Dim DesktopPath As String
        Dim strFileName As String

        Set WSHShell = CreateObject("WScript.Shell")
        DesktopPath = WSHShell.SpecialFolders("Desktop")
        Set WSHShell = Nothing

        strFileName = DesktopPath & "\" & ActiveWorkbook.Name
        ActiveSheet.ExportAsFixedFormat _
                Type:=xlTypePDF, _
                Filename:=strFileName, _
                Quality:=xlQualityStandard, _
                IncludeDocProperties:=True, _
                IgnorePrintAreas:=False, _
                OpenAfterPublish:=False

        Unload Me
        Unload UserForm4
        Sheets("Index").Visible = xlSheetHidden
        ActiveWorkbook.Protect Password:="", Structure:=True, Windows:=True
        MsgBox "Your PDF report has been placed on your Desktop"
    Else
        MsgBox "No check boxes were selected and/or selections not open"
        Sheets("Index").Visible = xlSheetHidden
        ActiveWorkbook.Protect Password:="", Structure:=True, Windows:=True
    End If
    Application.ScreenUpdating = True
End Sub

[ABabeNChrist]

thank you

[ABabeNChrist]

I wanted to know without changing the sheet name is it possible to change the sheet name that is added to the index page. Such as, the worksheet Pool Spa to be labeled as Electrical devices on the Index page and not the Pool Spa.

[HansV]

You already asked this before. The code in my previous reply does that. The array arrSheets contains the actual names of the worksheets, and the array arrNames contains the names as listed on the index sheet.
For example, the sheet Grounds is listed as Exterior Surfaces, and Structural Systems is listed as Interior Surfaces.
You can change other names in arrNames. Be very careful to change the correct ones!

[ABabeNChrist]

Perfect thank you Hans, they say your memory is the first to go and i probably had a double helping.....have a great day and hoping we all have a safe and happy new year

[HansV]

A happy and healthy new year to you too!

[ABabeNChrist]

You already asked this before. The code in my previous reply does that. The array arrSheets contains the actual names of the worksheets, and the array arrNames contains the names as listed on the index sheet.
For example, the sheet Grounds is listed as Exterior Surfaces, and Structural Systems is listed as Interior Surfaces.
You can change other names in arrNames. Be very careful to change the correct ones!

The changes you made work great with changing the names in the Index sheet, but now the page numbers are incorrect. The page number do not reflect the actual number of pages. The number are 1, 2, 3, 4,,,,,,, and so on. It seems to only count the number of sheets, not actual pages as it did before.

[HansV]

This thread has gone on for a very long time; I've lost track of all the modifications to the workbook and to the code. Could you attach a new sample workbook?

[ABabeNChrist]

in my sample workbook (much smaller workbook) it works great but in my larger workbook the page numbers do not follow correctly. Let me look around in the lager workbook and see what differences that may be causing this change.