The first 4 worksheets ("Cover Page", "Index", "Client Information", "Summary") and the last 2 worksheets ("Additional Photos", "Informational") will never change.
ONLY ("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) MAY CHANGE. The target range is A7 for each of the selected worksheets.
NOTED HANS HAS ASSISTED ME PREVIOUSLY ON AN OLDER THREAD
Code: Select all
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
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
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 and/or selections not open"
Sheets("Index").Visible = xlSheetHidden
ActiveWorkbook.Protect PassWord:="", Structure:=True, Windows:=True
End If
Application.ScreenUpdating = True