Page index sheet
-
- Administrator
- Posts: 78586
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Page index sheet
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
Hans
-
- SilverLounger
- Posts: 1868
- Joined: 25 Jan 2010, 14:00
- Location: Conroe, Texas
Re: Page index sheet
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.
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: Page index sheet
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.
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
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
-
- Administrator
- Posts: 78586
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Page index sheet
What? After we bloodily created that code?
Perhaps you could remove the lines
and
Perhaps you could remove the lines
Code: Select all
Sheets("Index").Range("C5").Value = " Page # "
Code: Select all
wshI.Range("C" & lngRow) = lngPage
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 1868
- Joined: 25 Jan 2010, 14:00
- Location: Conroe, Texas
Re: Page index sheet
That works great Hans. One last thing can i change the code
to a smaller range, something like
Code: Select all
wshI.Cells.ClearContents
Code: Select all
wshI.Range("A1:B30").ClearContents
-
- Administrator
- Posts: 78586
- 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
-
- SilverLounger
- Posts: 1868
- Joined: 25 Jan 2010, 14:00
- Location: Conroe, Texas
Re: Page index sheet
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
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
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
-
- Administrator
- Posts: 78586
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
-
- Administrator
- Posts: 78586
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Page index sheet
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.
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
Hans
-
- SilverLounger
- Posts: 1868
- Joined: 25 Jan 2010, 14:00
- Location: Conroe, Texas
Re: Page index sheet
I have attached a PDF sample. Notice on the the Summary page the text in red. The verbiage is on page number of report.
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78586
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Page index sheet
I'm afraid it's too complicated.
Please note that the page numbers on the Inspection Report Directory sheet aren't accurate either.
Please note that the page numbers on the Inspection Report Directory sheet aren't accurate either.
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 1868
- Joined: 25 Jan 2010, 14:00
- Location: Conroe, Texas
Re: Page index sheet
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.
-
- SilverLounger
- Posts: 1868
- Joined: 25 Jan 2010, 14:00
- Location: Conroe, Texas
Re: Page index sheet
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
-
- Administrator
- Posts: 78586
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Page index sheet
What code are you referring to? This is a very long thread...
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 1868
- Joined: 25 Jan 2010, 14:00
- Location: Conroe, Texas
Re: Page index sheet
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
-
- Administrator
- Posts: 78586
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Page index sheet
Thanks. What exactly do you mean by
That is rather confusing to me - do you want to change a sheet name or not?I was hoping to change only the value of the sheet name for this index page only, not the actual sheet name
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 1868
- Joined: 25 Jan 2010, 14:00
- Location: Conroe, Texas
Re: Page index sheet
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
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
-
- SilverLounger
- Posts: 1868
- Joined: 25 Jan 2010, 14:00
- Location: Conroe, Texas
Re: Page index sheet
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"