VBA Multiple "conditional" page breaks

[DoMa2906]

It turns out that the HPageBreaks collection object (and the VPageBreaks as well) cannot be accessed via VBA if the worksheet in question is in page break preview mode.
I have modified the existing code.


Sub KeepRangeTogether()
Dim ws As Worksheet
Set ws = Worksheets("ORIGINAL") 'define worksheet

Excel.Application.ScreenUpdating = False
With ws
.ResetAllPageBreaks 'remove all manual page breaks
'(only needed if this code is run multiple times on the same sheet)

SetHorPageBreak .Range("A1:A20") 'define range you wish to keep together
SetHorPageBreak .Range("A21:A40") 'define range you wish to keep together
'
End With
Excel.Application.ScreenUpdating = True
End Sub

Public Sub SetHorPageBreak(ByVal argRange As Range)
Dim pb As HPageBreak, wb As Workbook, ws As Worksheet, vw As XlWindowView

Set wb = ActiveSheet.Parent
Set ws = ActiveSheet
argRange.Parent.Parent.Activate
argRange.Parent.Activate
vw = ActiveWindow.View
ActiveWindow.View = xlNormalView

For Each pb In argRange.Parent.HPageBreaks 'loop through all page breaks
If Not Intersect(pb.Location, argRange) Is Nothing Then 'if a page break intersects your RangeToKeep
argRange.EntireRow.PageBreak = xlPageBreakManual 'insert manual page break
Exit For
End If
Next pb

ActiveWindow.View = vw
wb.Activate
ws.Activate
End Sub

(I am coming from this thread: "conditionnal" Page break - How to simplify / shorten code)


Hi,

I am working on a contract creator and wanted to include that code, because it is very convinient. I am having some trouble making it fully work.

First off: The workbook has one input sheet and several other sheets which use the input information to be displayed in them. I'm calling with different buttons a row of serveral tasks, which are including reseting hidden rows, rehiding them according to the updates, selecting the necessary sheets and using the Application.CommandBars.ExecuteMso "PrintPreviewAndPrint" to print / save as pdf. One of the sheets, which is selected, is the where I want to implement that function. The sheet contains blocks of different terms, which vary in length according to the input. Empty rows will be hidden. Block range (for example A64:A71) stays the same all the time.

In my example only the first necessary page break is done and all the other are ignored.

Am I missing something?

Thanks in advance

 

[DoMa2906]

After some more testing I came to the conclusion it, that it should have to do with the hidden rows, which are being considered. Is there a way to ignore them?

 

[DoMa2906]

Hi together,

After more trying and testing, I have found out some more stuff. As explained earlier, parts of the defined blocks could be empty and therefore will be hidden through a separate function in my worksheet. Those partly hidden defined blocks seem to work fine with the conditional page break, as long as I am on the defined worksheet while executing the macro. (That's fine for me)
But some blocks could be completely hidden, according to a condition in the master worksheet. When those blocks are completely hidden, the conditional page break (from that block onwards) doesn't work correctly anymore.

I thought of a workaround for that conclusion and I think that could work, if I make those "completely-hidable-blocks" conditional in my code as well. Please refer the code bellow. The commented ranges are the ones, which should be conditional. However, my knowledge of VBA is too limited to integrate an if-functionality for those ranges. I tried to write it in a standard Excel-manner for you to hopefully understand what I mean. The condition for hidden or not is the respective cell value in the "Data" worksheet. The value (written word) for "block active" = "true" and for "block inactive" = "false".

Can somebody help me with that?

Sub KeepRangeTogetherAngebot2()
    Dim ws As Worksheet
    Set ws = Worksheets("Angebot2")                'define worksheet
 
    Excel.Application.ScreenUpdating = False
    With ws
        .ResetAllPageBreaks                      'remove all manual page breaks
        '(only needed if this code is run multiple times on the same sheet)

        SetHorPageBreak .Range("A17:A33")        'This range should only be active, when this Formula is true =IF(Data!$B100="true";"Range is active";"Range should be ignored/skipped")
        SetHorPageBreak .Range("A35:A47")        'This range should only be active, when this Formula is true =IF(Data!$B119="true";"Range is active";"Range should be ignored/skipped")
        SetHorPageBreak .Range("A51:A62")        'Always active
        SetHorPageBreak .Range("A64:A71")        'This range should only be active, when this Formula is true =IF(Data!$B78="true";"Range is active";"Range should be ignored/skipped")
        SetHorPageBreak .Range("A92:A101")       'Always active
              '
    End With
    Excel.Application.ScreenUpdating = True
End Sub

Public Sub SetHorPageBreak(ByVal argRange As Range)
    Dim pb As HPageBreak, wb As Workbook, ws As Worksheet, vw As XlWindowView

    Set wb = ActiveSheet.Parent
    Set ws = ActiveSheet
    argRange.Parent.Parent.Activate
    argRange.Parent.Activate
    vw = ActiveWindow.View
    ActiveWindow.View = xlNormalView

    For Each pb In argRange.Parent.HPageBreaks                    'loop through all page breaks
        If Not Intersect(pb.Location, argRange) Is Nothing Then   'if a page break intersects your RangeToKeep
            argRange.EntireRow.PageBreak = xlPageBreakManual      'insert manual page break
            Exit For
        End If
    Next pb

    ActiveWindow.View = vw
    wb.Activate
    ws.Activate
End Sub

 

[Domenic]

Is this what you mean?

    With ws
    
        .ResetAllPageBreaks                      'remove all manual page breaks
        '(only needed if this code is run multiple times on the same sheet)

        If Worksheets("Data").Range("B100").Value = True Then
            SetHorPageBreak .Range("A17:A33")    'This range should only be active, when this Formula is true =IF(Data!$B100="true";"Range is active";"Range should be ignored/skipped")
        End If
        
        If Worksheets("Data").Range("B119").Value = True Then
            SetHorPageBreak .Range("A35:A47")    'This range should only be active, when this Formula is true =IF(Data!$B119="true";"Range is active";"Range should be ignored/skipped")
        End If
        
        SetHorPageBreak .Range("A51:A62")        'Always active
        
        If Worksheets("Data").Range("B78").Value = True Then
            SetHorPageBreak .Range("A64:A71")    'This range should only be active, when this Formula is true =IF(Data!$B78="true";"Range is active";"Range should be ignored/skipped")
        End If
        
        SetHorPageBreak .Range("A92:A101")       'Always active

    End With