Collapsed Section / Hidden page, prints as a blank page

[richard11153]

Hi,
I have some code that "expands / collapses" a page in a sheet. (Page #2 hides, or is viewable with a button click)
It works fine, but when I print, or print preview, page #2 still shows / prints, as a blank page.
I would want any "collapsed pages" not to print. Can someone help with this.
I attached a sample sheet of what I'm doing.
Excel 2016

[Greg M]

Hi there,

You're displaying/hiding the required rows all right, but the manual (horizontal) PageBreaks are still there, and these are what give rise to the empty pages when printing.

Your toggling routine also needs to insert/remove PageBreaks to avoid the empty pages - see if the following code does what you need:

Please Login or Register  to view this content.

The highlighted values may be altered to suit your requirements.


Hope this helps - please let me know how you get on.

Regards,

Greg M

[richard11153]

Thank you Greg. Yes this works fine, but when I altered the code for the "real sheet" (Adding pages)
It does not work. I'm sure I am doing something wrong.
The final code would be used to "expand / collapse" pages 4 through 30. (I can edit the code for lines collapsed).
Would it be asking too much to have you do that code for me?
Sorry, but I'm bad at coding.
Regards / Richard

[richard11153]

Even if you could do a few more page, I can then see the differences, and then attempt to do it myself.
Thanks Again.

[Greg M]

Hi again Richard,

Ok, thanks for your feedback.

Two questions:

You suggest that you'll need to be able to toggle Pages 4 - 30. It definitely makes life easier if you don't need to toggle Page 1 - can you confirm that this is the case?

How to you intend to call the various toggling routines? Will you have a different button for each page? Will you call them from the Macro menu (Alt+F8)? Would it be useful to have a cell with a 4-30 dropdown list from which the number of the page to be toggled could be selected? Some other method?

The answer to the second question will probably have a significant effect on the choice of the coding involved.

Regards,

Greg M

[richard11153]

Yes, Pages 1-3 will not need to have toggles. Then there may be about 6 or 8 pages that have toggles to "open or collapse" them.
The method of "toggling", I was thinging of just having a button, (just off the page on the lower right) to open and close.
I attached a workbook here of what I'm thinking, take a look. Also note, I was able to edit your code a bit to change and add pages.
It works ok with ONE EXCEPTION. Edited, I still have problems on some pages where the collapsed pages still show as "blank pages"
when printed. (I'm sure i'm screwing up the code changes but take a look at it.)

[richard11153]

Also, the "4-30 dropdown list" you mention is intriguing. (may be good). Is that something that could be on, say, page two.
(I would not mind seeing a sample of what that would be like).
The wookbook is a report of about 30 page, and sometimes I need a few "extra pages". (thus the toggled pages)
There is also an "Index of pages"

[Greg M]

Hi again Richard,

Take a look at the attached workbook and see if it gets you moving in the right direction.

The main worksheet contains ten page ranges, all of which are "collapsible" except for the first one.

Page ranges are toggled by selecting a page number / page title from the dropdown list of a ComboBox located beside the first page range - having the page title available is probably a bit more intuitive than having to remember which page number refers to which information.

The following code is inserted in the VBA CodeModule for the main worksheet:

Please Login or Register  to view this content.

and the following code in a standard VBA CodeModule:

Please Login or Register  to view this content.

The highlighted values may be altered to suit your requirements.


Hope this helps - please let me know how you get on.

Regards,

Greg M

[richard11153]

Wow, That's cool, but I dont think that will work for me in this report.

If you would be so kind as to just edit your previous code, (below) so that I can "toggle / collapse"
pages; 6, 9, 12, 17, 20, 21, 24, that would be a great help.
Thanks again for all your help. (I have to learn more about coding!)


[code]
Option Explicit


'=========================================================================================
'=========================================================================================


Const msPAGE_1 As String = "1:20"
Const msPAGE_2 As String = "21:40"
Const msPAGE_3 As String = "41:60"


'=========================================================================================
'=========================================================================================


Sub TogglePage_1()

Call TogglePage(sPage:=msPAGE_1)

' If the range being toggled is Page 1, then the PageBreak to be toggled is on Page 2
If ActiveSheet.Range(msPAGE_1).EntireRow.Hidden = True Then
Call PageBreakIsInserted(sPage:=msPAGE_2, bTrueorFalse:=False)
Else: Call PageBreakIsInserted(sPage:=msPAGE_2, bTrueorFalse:=True)
End If

End Sub


'=========================================================================================
'=========================================================================================


Sub TogglePage_2()

Call TogglePage(sPage:=msPAGE_2)

End Sub


'=========================================================================================
'=========================================================================================


Sub TogglePage_3()

Call TogglePage(sPage:=msPAGE_3)

End Sub


'=========================================================================================
'=========================================================================================


Private Sub TogglePage(sPage As String)

Dim rPage As Range
Dim wks As Worksheet

Set wks = ActiveSheet

With wks

.Range(msPAGE_2).Rows(1).EntireRow.PageBreak = xlPageBreakManual
.Range(msPAGE_3).Rows(1).EntireRow.PageBreak = xlPageBreakManual

Set rPage = .Range(sPage)

End With

With rPage

.EntireRow.Hidden = Not .EntireRow.Hidden

' Insert/remove a PageBreak only if this is NOT the first page
If sPage <> msPAGE_1 Then

If .EntireRow.Hidden = True And .Rows(1).Row > 1 Then
.Rows(1).EntireRow.PageBreak = xlPageBreakNone
Else: .Rows(1).EntireRow.PageBreak = xlPageBreakManual
End If

End If

End With

End Sub


'=========================================================================================
'=========================================================================================


Private Sub PageBreakIsInserted(sPage As String, bTrueorFalse As Boolean)

With ActiveSheet.Range(sPage).Rows(1)

If bTrueorFalse = True Then
.PageBreak = xlPageBreakManual
Else: .PageBreak = xlPageBreakNone
End If

End With

End Sub
[code}

[Greg M]

Hi again,

Ok, see if the attached version of the workbook does what you need.


It uses the following code in the VBA CodeModule of the worksheet in question:

Please Login or Register  to view this content.

and the following code in a standard VBA CodeModule:

Please Login or Register  to view this content.

The highlighted values may be altered to suit your requirements.


Hope this helps - as before, please let me know how you get on.

Regards,

Greg M