Page index sheet

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Page index sheet

Post by HansV »

Might be, but I think it's more likely it has to do with the printer driver; I don't have a solution though.
Best wishes,
Hans

ABabeNChrist
SilverLounger
Posts: 1868
Joined: 25 Jan 2010, 14:00
Location: Conroe, Texas

Re: Page index sheet

Post by ABabeNChrist »

Most of the users use PDF Creator and set as there default printer. This is done so that all reports will be consistent with other users. Since different printer may view a worksheet differently.

User avatar
Rudi
gamma jay
Posts: 25455
Joined: 17 Mar 2010, 17:33
Location: Cape Town

Re: Page index sheet

Post by Rudi »

My laptop is running on Win 8.1 64 bit, but Office 2013 I have installed is 32 bit.
Regards,
Rudi

If your absence does not affect them, your presence didn't matter.

ABabeNChrist
SilverLounger
Posts: 1868
Joined: 25 Jan 2010, 14:00
Location: Conroe, Texas

Re: Page index sheet

Post by ABabeNChrist »

This code has been working good, but is it possible to remove page numbering portion of the code

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

    '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")

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

    Application.ScreenUpdating = False

    Sheets("Index").Visible = xlSheetVisible

    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) = 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 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
        ActiveWindow.SelectedSheets.Application.Dialogs(xlDialogPrint).Show
        Unload Me
        Unload UserForm4
        Sheets("Index").Visible = xlSheetHidden
        ActiveWorkbook.Protect Password:="", Structure:=True, Windows:=True
    Else
        MsgBox "No check boxes were selected"
        Sheets("Index").Visible = xlSheetHidden
        ActiveWorkbook.Protect Password:="", Structure:=True, Windows:=True
    End If
    Application.ScreenUpdating = True
End Sub

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Page index sheet

Post by HansV »

What? After we bloodily created that code? :yikes: :laugh:

Perhaps you could remove the lines

Code: Select all

    Sheets("Index").Range("C5").Value = " Page # "
and

Code: Select all

            wshI.Range("C" & lngRow) = lngPage
Best wishes,
Hans

ABabeNChrist
SilverLounger
Posts: 1868
Joined: 25 Jan 2010, 14:00
Location: Conroe, Texas

Re: Page index sheet

Post by ABabeNChrist »

That works great Hans. One last thing can i change the code

Code: Select all

wshI.Cells.ClearContents
to a smaller range, something like

Code: Select all

wshI.Range("A1:B30").ClearContents

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Page index sheet

Post by HansV »

Yes, of course.
Best wishes,
Hans

ABabeNChrist
SilverLounger
Posts: 1868
Joined: 25 Jan 2010, 14:00
Location: Conroe, Texas

Re: Page index sheet

Post by ABabeNChrist »

Thank you Hans :grin:

ABabeNChrist
SilverLounger
Posts: 1868
Joined: 25 Jan 2010, 14:00
Location: Conroe, Texas

Re: Page index sheet

Post by ABabeNChrist »

Hello, i have been using this approach to indexing page numbers on Worksheet("Inspection Report Directory") when the workbook sheets are selected then printed. Everything has been great. I have been asked if its possible to include a page number to any defects that i may insert to a summary page. Each worksheet are broken down to different categories, Such as worksheet named Utilities will have electrical service panels, Water utilities, Gas utilities and plumbing. Each worksheet can range from 1 to 5 pages in size with the categories in various areas.

Here is what I currently use to add a selected comment to the Summary worksheet while I am still on the selected sheet before selecting and printing sheet/numbers
I am hoping to add the page number after page value (A9, A27, A40 and A56

I do understand the page numbers can vary as I insert or remove info when filling out my reports

Any suggestions you have or a better process I am all ears :grin:

Code: Select all

Private Sub CommandButton1_Click()
    Dim blnSelected As Boolean

    ActiveWorkbook.Protect PassWord:="", Structure:=False, Windows:=False
    Sheets("Summary").Unprotect ""

    If CheckBox1.Value = True Then
        blnSelected = True
        Sheets("Summary").Range("A9:A10").EntireRow.Hidden = False
        Sheets("Summary").Range("A9").Value = Range("A9").Value & _
                                              ": " & Range("A12").Value
        Sheets("Summary").Range("A10").Value = "• " & Range("A20").Value
        Sheets("Summary").Range("A10").EntireRow.AutoFit
        Sheets("Summary").Range("A8").EntireRow.Hidden = False

    End If

    If CheckBox2.Value = True Then
        blnSelected = True
        Sheets("Summary").Range("A11:A12").EntireRow.Hidden = False
        Sheets("Summary").Range("A11").Value = Range("A27").Value & _
                                               ": " & Range("A31").Value
        Sheets("Summary").Range("A12").Value = "• " & Range("G33").Value
        Sheets("Summary").Range("A12").EntireRow.AutoFit
        Sheets("Summary").Range("A8").EntireRow.Hidden = False

    End If

    If CheckBox3.Value = True Then
        blnSelected = True
        Sheets("Summary").Range("A13:A14").EntireRow.Hidden = False
        Sheets("Summary").Range("A13").Value = Range("A40").Value & _
                                               ": " & Range("A44").Value
        Sheets("Summary").Range("A14").Value = "• " & Range("G46").Value
        Sheets("Summary").Range("A14").EntireRow.AutoFit
        Sheets("Summary").Range("A8").EntireRow.Hidden = False

    End If

    If CheckBox4.Value = True Then
        blnSelected = True
        Sheets("Summary").Range("A15:A16").EntireRow.Hidden = False
        Sheets("Summary").Range("A15").Value = Range("A56").Value & _
                                               ": " & Range("A59").Value
        Sheets("Summary").Range("A16").Value = "• " & Range("A65").Value
        Sheets("Summary").Range("A16").EntireRow.AutoFit
        Sheets("Summary").Range("A8").EntireRow.Hidden = False

    End If

        Sheets("Summary").UsedRange.Replace What:="• •", Replacement:="•", LookAt:=xlPart
        
        'Sheets("Summary").Protect DrawingObjects:=False, Contents:=True, Scenarios:=True, _
                              PassWord:="", UserInterFaceOnly:=True


    'ActiveWorkbook.Protect PassWord:="", Structure:=True, Windows:=True
    
    If blnSelected = True Then
        MsgBox "Your selection(s) have now been added to Report Summary page"
        Unload Me
        Unload UserForm5
    Else
        MsgBox "No check boxes were selected"
    End If
End Sub

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Page index sheet

Post by HansV »

This is difficult to follow without seeing the workbook.
Best wishes,
Hans

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Page index sheet

Post by HansV »

Thanks for sending me a copy of the workbook.

I'm afraid I can't match your description in your previous reply with the Summary sheet in the workbook. Please explain as clearly as you can where exactly you want page numbers, and how we should determine them.
Best wishes,
Hans

ABabeNChrist
SilverLounger
Posts: 1868
Joined: 25 Jan 2010, 14:00
Location: Conroe, Texas

Re: Page index sheet

Post by ABabeNChrist »

I have attached a PDF sample. Notice on the the Summary page the text in red. The verbiage is on page number of report.
Sample.pdf
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Page index sheet

Post by HansV »

I'm afraid it's too complicated.
Please note that the page numbers on the Inspection Report Directory sheet aren't accurate either.
Best wishes,
Hans

ABabeNChrist
SilverLounger
Posts: 1868
Joined: 25 Jan 2010, 14:00
Location: Conroe, Texas

Re: Page index sheet

Post by ABabeNChrist »

Thank you Hans I no it could be difficult to determine what page a targeted comments would end up on when multiple worksheets are consolidated into a single PDF.

ABabeNChrist
SilverLounger
Posts: 1868
Joined: 25 Jan 2010, 14:00
Location: Conroe, Texas

Re: Page index sheet

Post by ABabeNChrist »

I have made a copy of this workbook that uses this index code, but I was hoping to change only the value of the sheet name for this index page only, not the actual sheet name and I still want the page # to reflect the worksheet name

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Page index sheet

Post by HansV »

What code are you referring to? This is a very long thread...
Best wishes,
Hans

ABabeNChrist
SilverLounger
Posts: 1868
Joined: 25 Jan 2010, 14:00
Location: Conroe, Texas

Re: Page index sheet

Post by ABabeNChrist »

Here is the code I am currently using.

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

    '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")

    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) = 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 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

    'Sheets("Cover Page").Select

End Sub

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Page index sheet

Post by HansV »

Thanks. What exactly do you mean by
I was hoping to change only the value of the sheet name for this index page only, not the actual sheet name
That is rather confusing to me - do you want to change a sheet name or not?
Best wishes,
Hans

ABabeNChrist
SilverLounger
Posts: 1868
Joined: 25 Jan 2010, 14:00
Location: Conroe, Texas

Re: Page index sheet

Post by ABabeNChrist »

I only want to change the value in column B (sheet name) on the index sheet. Currently if populates the sheet names.
I want the
Grounds sheet value to be Exterior Surfaces
Structural Systems sheet value to be Interior Surfaces
Informational sheet value to be Laboratory Results

ABabeNChrist
SilverLounger
Posts: 1868
Joined: 25 Jan 2010, 14:00
Location: Conroe, Texas

Re: Page index sheet

Post by ABabeNChrist »

Could I add something like at the target locations this before print

Code: Select all

    Sheets("Index").Range("B11").Value = "Exterior Surfaces"
    Sheets("Index").Range("B12").Value = "Interior Surfaces"
    Sheets("Index").Range("B24").Value = "Laboratory Results"