Page index sheet

[syswizard]

Well done Hans. Select Case is far, FAR too under-used...instead it's replaced by complex if-then-elseif logic strings.

Select Case is so much more intuitive and clear as far as logic inference goes.
As soon as I get into nested "if-then-else" clauses, I now catch myself and convert to Select Case most of the time.

[ABabeNChrist]

I found the problem, after reviewing my previous code and the new code you provided, I noticed the original code used

Code: Select all

Sheets("Client Information").Select Replace:=False

And the new code used

Code: Select all

Sheets(arrSheets(i - 1)).Select Replace:=True

I then changed the True to False, and everything worked
Thank you so much Hans

[HansV]

Sorry, I got a bit overenthusiastic when I created the loops. Change the line

Code: Select all

            Sheets(arrSheets(i - 1)).Select Replace:=True

to

Code: Select all

            Sheets(arrSheets(i - 1)).Select Replace:=Not blnSelected

[ABabeNChrist]

I was just noticing when I made this change that the next line is blnSelected = True
Is that correct

Code: Select all

        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

[HansV]

Yes, that's intentional.

[ABabeNChrist]

Thank you Hans, your assistance is always greatly appreciated.

[ABabeNChrist]

Upon further use of code below I encountered a couple concerns
1. The first concern was if I run the code and then opt out of the xlDialogPrint by selecting the cancel button if the activesheet has a shape it then becomes distorted (the shape only) but if I select print preview it all looks fine and when I return to normal view it all looks good again. Puzzling…
2. The next thing is if the cover page is hidden and then of course is not selected from the userform print page I receive an error at the end of the code on this line “ActiveSheet.Select”

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", "Bathroom(s)", "Kitchen", "Kitchen Appliances", _
                      "Heating and Cooling", "Water Heater", "Pool Spa", "Additional Photos", _
                      "Additional Photos 2")

    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 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:=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
        Sheets("Cover Page").Activate
        Sheets("Cover Page").Range("A1").Select
        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
    ActiveSheet.Select
End Sub

[HansV]

I can't explain #1, I'd have to see the workbook.
As for #2, simply delete the offending line.

[ABabeNChrist]

Thanks Hans, I’ll play around with it a little more and try and see what may be cause #1 concern

[ABabeNChrist]

It’s working better but I’m still having a problem with this code below and I’m not sure why.
For some unknown reason I’m unable to even print “Kitchen Appliances” sheet even with single or multiple print selections. The sheet name is correctly spelled and the sheet is visible. When I only select the Kitchen Appliances sheet for printing it will print a different sheet like Laundry.

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

[HansV]

I can't reproduce the error. I'd have to see the workbook.

[ABabeNChrist]

It took some doing but I was able to recreate a small enough workbook. I added a print button on the cover page worksheet that will open userform that I use in my original workbook for printing.

sample print sheets.xlsm

[HansV]

The numbering of the check boxes in the second and third column is off - it doesn't correspond to order of the sheets in the array. For example, the first column ends with CheckBox10, labeled "Fireplace and Chimney". This is correct - "Fireplace and Chimney" is the 10th item in the array. But the first check box in the second column is Checkbox21, labeled "Interior/Living Areas". This is incorrect - "Interior" is the 11th item in the array. So this check box should be named CheckBox11, not CheckBox21, etc.

[ABabeNChrist]

Thank you Hans, that worked perfect

[ABabeNChrist]

I have been using this approach for a while flawlessly (Windows 7 and Office 2007), but I have another user that mentioned that the worksheet page # order is incorrect. While some sheets may have more than one page the index only reflects one page per sheet. The user uses Windows 8 with Office 2013

[HansV]

I have neither Windows 8 nor Excel 2013, so I can't help with this.

[Rudi]

I tested with Win 8.1 and Excel 2013. All is fine on this side.
I added content to several pages and ran the reporting tool and the order and pg #'s seem fine....

1.jpg

[HansV]

Thanks for testing, Rudi!

[Rudi]

I've done a few more tests, which all worked out well with various lengths of data.
There was only one little discrepancy.

Due to a run over of two records on a third page, it caused the numbering to be out by one page...
This only happened once? Not to sure if it was a fluke?

Notice Interior on pg 14

1.jpg

Fireplace has a slight spill over with 2 records on PG 14

2.jpg

Interior now starts on PG 15 where the Index says 14

3.jpg

[ABabeNChrist]

Thank you guys, I’m wondering if it’s a 64 bit thing