Collapsed Section / Hidden page, prints as a blank page

**richard11153** · 10-22-2019, 02:09 PM

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** · 10-23-2019, 11:42 AM

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:




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

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** · 10-23-2019, 12:13 PM

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** · 10-24-2019, 07:38 AM

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** · 10-24-2019, 07:50 AM

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** · 10-24-2019, 09:09 AM

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"

**richard11153** · 10-24-2019, 09:04 AM

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.)

**Greg M** · 10-24-2019, 05:01 PM

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:




Option Explicit


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


Private Sub cmbPageNo_DropButtonClick()

    Dim vaDropdownList As Variant

    With Me.cmbPageNo

'       If appropriate (i.e. if the dropdown list contains no items), populate the
'       dropdown list of the Page No ComboBox
        If .ListCount = 0 Then

            .Clear

            vaDropdownList = gvaDropdownList()
            .List = vaDropdownList

            .ColumnWidths = "20, 90"
            .ColumnCount = 2
            .ListRows = 16

        End If

    End With

End Sub


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


Private Sub cmbPageNo_Change()

'   Proceed only if a value has been selected from the dropdown list
    If Me.cmbPageNo.Value <> vbNullString Then

        Call TogglePage(iPageNo:=Me.cmbPageNo.Value)

'       Clear the currently-displayed value from the ComboBox - if this is not done,
'       selecting that value again (to immediately re-toggle the worksheet) will not
'       count as a Change event, and this routine will not be triggered
        Me.cmbPageNo.Value = vbNullString

    End If

'   Move the focus away from the ComboBox
    ActiveCell.Activate

End Sub

and the following code in a standard VBA CodeModule:




Option Explicit


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


Const miROWS_PER_PAGE   As Integer = 20
Const miFIRST_PAGE_NO   As Integer = 2      '   <<<<    The first "collapsible" worksheet
Const miNO_OF_PAGES     As Integer = 9


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


Public Function gvaDropdownList() As Variant

    Dim vaDropdownList As Variant

    ReDim vaDropdownList(1 To miNO_OF_PAGES, 1 To 2)

    vaDropdownList(1, 1) = Format(miFIRST_PAGE_NO, "00")
    vaDropdownList(2, 1) = Format(miFIRST_PAGE_NO + 1, "00")
    vaDropdownList(3, 1) = Format(miFIRST_PAGE_NO + 2, "00")
    vaDropdownList(4, 1) = Format(miFIRST_PAGE_NO + 3, "00")
    vaDropdownList(5, 1) = Format(miFIRST_PAGE_NO + 4, "00")
    vaDropdownList(6, 1) = Format(miFIRST_PAGE_NO + 5, "00")
    vaDropdownList(7, 1) = Format(miFIRST_PAGE_NO + 6, "00")
    vaDropdownList(8, 1) = Format(miFIRST_PAGE_NO + 7, "00")
    vaDropdownList(9, 1) = Format(miFIRST_PAGE_NO + 8, "00")

    vaDropdownList(1, 2) = "Names"
    vaDropdownList(2, 2) = "Addresses"
    vaDropdownList(3, 2) = "Departments"
    vaDropdownList(4, 2) = "Divisions"
    vaDropdownList(5, 2) = "Phone Nos"
    vaDropdownList(6, 2) = "Cities"
    vaDropdownList(7, 2) = "Budgets"
    vaDropdownList(8, 2) = "Expenses"
    vaDropdownList(9, 2) = "Incomes"

    gvaDropdownList = vaDropdownList

End Function


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


Private Function mvaPageRanges() As Variant

    Dim vaPageRanges    As Variant
    Dim iPageNo         As Integer
    Dim wks             As Worksheet

    ReDim vaPageRanges(miFIRST_PAGE_NO To (miFIRST_PAGE_NO + miNO_OF_PAGES - 1))

    Set wks = ActiveSheet

    With wks

        For iPageNo = LBound(vaPageRanges) To UBound(vaPageRanges)

            Set vaPageRanges(iPageNo) = Range(.Rows(miROWS_PER_PAGE * (iPageNo - 1) + 1), _
                                              .Rows(miROWS_PER_PAGE * iPageNo))

        Next iPageNo

    End With

    mvaPageRanges = vaPageRanges

End Function


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


Public Sub TogglePage(iPageNo As Integer)

    Dim vaPageRanges    As Variant
    Dim vPageRange      As Variant

    vaPageRanges = mvaPageRanges()

    For Each vPageRange In vaPageRanges
        vPageRange.Rows(1).EntireRow.PageBreak = xlPageBreakManual
    Next vPageRange

    With vaPageRanges(iPageNo)

        .EntireRow.Hidden = Not .EntireRow.Hidden

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

    End With

End Sub

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** · 10-26-2019, 10:40 AM

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** · 10-29-2019, 07:09 AM

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:



Option Explicit


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


Private Sub cmbPageNo_DropButtonClick()

    Dim vaDropdownList As Variant

    With Me.cmbPageNo

'       If appropriate (i.e. if the dropdown list contains no items),
'       populate the dropdown list of the Page No ComboBox
        If .ListCount = 0 Then

            vaDropdownList = gvaPageDetails()
            .List = vaDropdownList

'           Display only the Page No and Page Title in the dropdown list
            .ColumnWidths = "20, 90, 0"
            .ColumnCount = 3
            .ListRows = 16

        End If

    End With

End Sub


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


Private Sub cmbPageNo_Change()

    Dim iPageNo As Integer

    With Me.cmbPageNo

'       Proceed only if a value has been selected from the dropdown list
        If .Value <> vbNullString Then

            iPageNo = .ListIndex + 1

            Call TogglePage(iPageNo:=iPageNo)

'           Clear the currently-displayed value from the ComboBox - if this is not done,
'           selecting that value again (to immediately re-toggle the worksheet) will not
'           count as a Change event, and this routine will not be triggered
            .ListIndex = -1

        End If

    End With

'   Move the focus away from the ComboBox
    ActiveCell.Activate

End Sub

and the following code in a standard VBA CodeModule:




Option Explicit


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


Const miINDEX__RANGE    As Integer = 3
Const miNO_OF_PAGES     As Integer = 7


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


Public Function gvaPageDetails() As Variant

    Dim vaPageDetails   As Variant
    Dim wks             As Worksheet

    ReDim vaPageDetails(1 To miNO_OF_PAGES, 1 To 3)

'   Page Numbers
    vaPageDetails(1, 1) = Format(6, "00")
    vaPageDetails(2, 1) = Format(9, "00")
    vaPageDetails(3, 1) = Format(12, "00")
    vaPageDetails(4, 1) = Format(17, "00")
    vaPageDetails(5, 1) = Format(20, "00")
    vaPageDetails(6, 1) = Format(21, "00")
    vaPageDetails(7, 1) = Format(24, "00")

'   Page Titles
    vaPageDetails(1, 2) = "Names"
    vaPageDetails(2, 2) = "Addresses"
    vaPageDetails(3, 2) = "Departments"
    vaPageDetails(4, 2) = "Divisions"
    vaPageDetails(5, 2) = "Phone Nos"
    vaPageDetails(6, 2) = "Cities"
    vaPageDetails(7, 2) = "Budgets"

'   Page Ranges
    Set wks = ActiveSheet

    With wks

        Set vaPageDetails(1, miINDEX__RANGE) = .Range("101:120")
        Set vaPageDetails(2, miINDEX__RANGE) = .Range("161:180")
        Set vaPageDetails(3, miINDEX__RANGE) = .Range("221:240")
        Set vaPageDetails(4, miINDEX__RANGE) = .Range("321:340")
        Set vaPageDetails(5, miINDEX__RANGE) = .Range("381:400")
        Set vaPageDetails(6, miINDEX__RANGE) = .Range("401:420")
        Set vaPageDetails(7, miINDEX__RANGE) = .Range("461:480")

    End With

    gvaPageDetails = vaPageDetails

End Function


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


Public Sub TogglePage(iPageNo As Integer)

    Dim vaPageDetails   As Variant
    Dim rPageRange      As Range

    vaPageDetails = gvaPageDetails()

    Call ResetPageBreaksIfAppropriate(vaPageDetails:=vaPageDetails)

    Set rPageRange = vaPageDetails(iPageNo, miINDEX__RANGE)

    With rPageRange

        .EntireRow.Hidden = Not .EntireRow.Hidden

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

    End With

End Sub


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


Private Sub ResetPageBreaksIfAppropriate(vaPageDetails As Variant)

    Dim rPageRange  As Range
    Dim iPageNo     As Integer

    vaPageDetails = gvaPageDetails()

    For iPageNo = 1 To UBound(vaPageDetails, 1)

        Set rPageRange = vaPageDetails(iPageNo, miINDEX__RANGE)

        With rPageRange.Rows(1).EntireRow

            If .Hidden = False Then
                .PageBreak = xlPageBreakManual
            End If

        End With

    Next iPageNo

End Sub

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

Collapsed Section / Hidden page, prints as a blank page

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Collapsed Section / Hidden page, prints as a blank page

Re: Collapsed Section / Hidden page, prints as a blank page

Re: Collapsed Section / Hidden page, prints as a blank page

Re: Collapsed Section / Hidden page, prints as a blank page

Re: Collapsed Section / Hidden page, prints as a blank page

Re: Collapsed Section / Hidden page, prints as a blank page

Re: Collapsed Section / Hidden page, prints as a blank page

Re: Collapsed Section / Hidden page, prints as a blank page

Re: Collapsed Section / Hidden page, prints as a blank page

Re: Collapsed Section / Hidden page, prints as a blank page

Thread Information

Users Browsing this Thread

Similar Threads

Excel 2016 - Chart displays on screen but prints blank page

[SOLVED] Page Layout (Page Break Preview) is not working with hidden rows in excel.

[SOLVED] Having difficultly moving a section from page 3 to page 2 in a word document form

Excel 2010 prints first extra blank page

Include a separator page (blank) after a specified amount of prints

First page prints normal, other pages print blank, why?

When I'm trying to print a document, an extra blank page prints

Bookmarks

Bookmarks

Posting Permissions