Page index sheet
-
- 4StarLounger
- Posts: 584
- Joined: 12 Jul 2012, 10:34
Re: Page index sheet
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.
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.
-
- SilverLounger
- Posts: 1868
- Joined: 25 Jan 2010, 14:00
- Location: Conroe, Texas
Re: Page index sheet
I found the problem, after reviewing my previous code and the new code you provided, I noticed the original code used
And the new code used
I then changed the True to False, and everything worked
Thank you so much Hans
Code: Select all
Sheets("Client Information").Select Replace:=False
Code: Select all
Sheets(arrSheets(i - 1)).Select Replace:=True
Thank you so much Hans
-
- Administrator
- Posts: 78647
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Page index sheet
Sorry, I got a bit overenthusiastic when I created the loops. Change the line
to
Code: Select all
Sheets(arrSheets(i - 1)).Select Replace:=True
Code: Select all
Sheets(arrSheets(i - 1)).Select Replace:=Not blnSelected
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 1868
- Joined: 25 Jan 2010, 14:00
- Location: Conroe, Texas
Re: Page index sheet
I was just noticing when I made this change that the next line is blnSelected = True
Is that correct
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
-
- Administrator
- Posts: 78647
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
-
- SilverLounger
- Posts: 1868
- Joined: 25 Jan 2010, 14:00
- Location: Conroe, Texas
Re: Page index sheet
Thank you Hans, your assistance is always greatly appreciated.
-
- SilverLounger
- Posts: 1868
- Joined: 25 Jan 2010, 14:00
- Location: Conroe, Texas
Re: Page index sheet
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”
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
-
- Administrator
- Posts: 78647
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Page index sheet
I can't explain #1, I'd have to see the workbook.
As for #2, simply delete the offending line.
As for #2, simply delete the offending line.
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 1868
- Joined: 25 Jan 2010, 14:00
- Location: Conroe, Texas
Re: Page index sheet
Thanks Hans, I’ll play around with it a little more and try and see what may be cause #1 concern
-
- SilverLounger
- Posts: 1868
- Joined: 25 Jan 2010, 14:00
- Location: Conroe, Texas
Re: Page index sheet
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.
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
-
- Administrator
- Posts: 78647
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
-
- SilverLounger
- Posts: 1868
- Joined: 25 Jan 2010, 14:00
- Location: Conroe, Texas
Re: Page index sheet
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.
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78647
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Page index sheet
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.
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 1868
- Joined: 25 Jan 2010, 14:00
- Location: Conroe, Texas
Re: Page index sheet
Thank you Hans, that worked perfect
-
- SilverLounger
- Posts: 1868
- Joined: 25 Jan 2010, 14:00
- Location: Conroe, Texas
Re: Page index sheet
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
-
- Administrator
- Posts: 78647
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Page index sheet
I have neither Windows 8 nor Excel 2013, so I can't help with this.
Best wishes,
Hans
Hans
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: Page index sheet
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....
I added content to several pages and ran the reporting tool and the order and pg #'s seem fine....
You do not have the required permissions to view the files attached to this post.
Regards,
Rudi
If your absence does not affect them, your presence didn't matter.
Rudi
If your absence does not affect them, your presence didn't matter.
-
- Administrator
- Posts: 78647
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: Page index sheet
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 Fireplace has a slight spill over with 2 records on PG 14 Interior now starts on PG 15 where the Index says 14
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 Fireplace has a slight spill over with 2 records on PG 14 Interior now starts on PG 15 where the Index says 14
You do not have the required permissions to view the files attached to this post.
Regards,
Rudi
If your absence does not affect them, your presence didn't matter.
Rudi
If your absence does not affect them, your presence didn't matter.
-
- SilverLounger
- Posts: 1868
- Joined: 25 Jan 2010, 14:00
- Location: Conroe, Texas
Re: Page index sheet
Thank you guys, I’m wondering if it’s a 64 bit thing