Page break Excel Addin

Page Break:

Page break determines page boundary while printing your data. Excel offers two types of Page breaks in a sheet:

1. Horizontal Break: puts horizontal break in sheet
2. Vertical Break: puts vertical break in sheet

In Excel we can insert page breaks (Inserts horizontal and vertical breaks both), remove page breaks (removes horizontal and vertical breaks both) and reset all page breaks. Follow the steps to put a page break in Excel:

• Highlight range from where you want to put break
• Select Page Layout ribbon tab
• Select Breaks dropdown from Page Setup group as shown below
• Select Insert Page Break command

Let’s put some code to build an automated solution using VBA, VB.Net and VSTO (Visual Studio Office for Tools) C#:

VSTO C# code example: Insert page break

private void btnInsertPageBreak_Click(object sender, RibbonControlEventArgs e)
{
	//reference active sheet
	excel.Worksheet oWorksheet = Globals.ThisAddIn.Application.ActiveSheet;


	//retaing user selection 
	excel.Range oRange = Globals.ThisAddIn.Application.Selection;


	//Insert Horizontal page break
	oWorksheet.HPageBreaks.Add(Before: oRange);

	//Insert Vertical page break
	oWorksheet.VPageBreaks.Add(Before: oRange);
}

VSTO C# code example: Remove page break

private void btnRemovePageBreak_Click(object sender, RibbonControlEventArgs e)
{
	//reference active sheet
	excel.Worksheet oWorksheet = Globals.ThisAddIn.Application.ActiveSheet;

	//remove all horizontal breaks
	foreach (excel.HPageBreak hBreak in oWorksheet.HPageBreaks)
	{
		hBreak.Delete();
	}


	//remove all vertical breaks
	foreach (excel.VPageBreak vBreak in oWorksheet.VPageBreaks)
	{
		vBreak.Delete();
	}
}

VSTO C# code example: Reset all page breaks

private void btnResetAllPageBreaks_Click(object sender, RibbonControlEventArgs e)
{
	//reference active sheet
	excel.Worksheet oWorksheet = Globals.ThisAddIn.Application.ActiveSheet;

	//reset all page breaks
	oWorksheet.ResetAllPageBreaks();
}

VB.Net code example: Insert page break

 Private Sub InsertPageBreak_Click(sender As Object, e As RibbonControlEventArgs) Handles InsertPageBreak.Click
        'Bind worksheet object reference
        Dim oWorkSheet as excel.Worksheet
        oWorkSheet = Globals.ThisAddIn.Application.ActiveSheet

        'Bind Range object from selection
        Dim oRange As excel.Range
        oRange=Globals.ThisAddIn.Application.Selection

        'insert horizontal break
        oWorkSheet.HPageBreaks.Add(Before := oRange)

        'insert vertical break
        oWorkSheet.VPageBreaks.Add(Before := oRange)

    End Sub

VB.Net code example: Remove page break

Private Sub RemoveBreaks_Click(sender As Object, e As RibbonControlEventArgs) Handles RemoveBreaks.Click
        'Bind worksheet object reference
        Dim oWorkSheet as excel.Worksheet
        oWorkSheet = Globals.ThisAddIn.Application.ActiveSheet

        'Bind Range object from selection
        Dim oRange As excel.Range
        oRange=Globals.ThisAddIn.Application.Selection

        'remove all horizontal breaks
        For Each hBreak As excel.HPageBreak In oWorkSheet.HPageBreaks
            hBreak.Delete()
        Next
        
        'remove all vertical breaks
        For Each vBreak As excel.VPageBreak In oWorkSheet.VPageBreaks
            vBreak.Delete()
        Next

    End Sub

VB.Net code example: Reset all pages

 Private Sub ResetAllPages_Click(sender As Object, e As RibbonControlEventArgs) Handles ResetAllPages.Click
        'Bind worksheet object reference
        Dim oWorkSheet as excel.Worksheet
        oWorkSheet = Globals.ThisAddIn.Application.ActiveSheet

        'Reset all pages
        oWorkSheet.ResetAllPageBreaks()

    End Sub

VBA code example: Insert page break

Public Sub InsertPageBreak()
    'insert horizontal page break
    ActiveSheet.HPageBreaks.Add Before:=ActiveCell
    
    'insert vertical page break
    ActiveSheet.VPageBreaks.Add Before:=ActiveCell
End Sub

VBA code example: Remove page break

Public Sub RemovePageBreak()
    'declare local variables
    Dim hBreak As HPageBreak
    Dim vBreak As VPageBreak
    
    'delete all horizontal breaks
    For Each hBreak In ActiveSheet.HPageBreaks
        hBreak.Delete
    Next hBreak
    
    'delete all vertical breaks
    For Each vBreak In ActiveSheet.VPageBreaks
        vBreak.Delete
    Next vBreak
End Sub

VBA code example: Reset all page breaks

Public Sub ResetallPageBreaks()
    ActiveSheet.ResetallPageBreaks
End Sub

Share on Facebook

Tweet