Show Hide Headings in Excel Add-in with example

Show Hide Headings in Excel Add-in with example

Headings

Refers to column headings in a sheet. It helps user to understand column name by identifying labels on the heading as shown below:

In excel 2020 onward the heading starting from A and goes upto XFD. In this article we will learn how to playing around headings in Excel sheet.

Show/Hide Headings

By default in Excel all headings are in visible state as shown above. But sometimes user may in need to hide the same. Let’s see how we can hide the headings in a sheet.

  • Navigate to Page Layout Ribbon Tab (Screenshot given below)
  • Uncheck View checkbox under Sheet Options group
  • Now you notice the column headings are disappeared

Before

After

Note: Headings are derived through Window object which comes from Application parent hence we have to bind window reference to toggle the DisplayHeadings property. Headings property is a boolean which takes/returns true or false as shown below:

C# code example

private void bthHeadings_Click(object sender, RibbonControlEventArgs e)
{
	//get window reference
	excel.Window oWindow = Globals.ThisAddIn.Application.ActiveWindow;

	//check if headings are visible then hide
	if (oWindow.DisplayHeadings == true)
		oWindow.DisplayHeadings = false;
	else
		oWindow.DisplayHeadings = true;
}

VB.Net code example

Private Sub DisplayHeadings_Click(sender As Object, e As RibbonControlEventArgs) Handles DisplayHeadings.Click
        'Bind window reference object
        dim oWindow as excel.Window
        oWindow = Globals.ThisAddIn.Application.ActiveWindow()

        'check if headings are visible then hide
        If oWindow.DisplayHeadings=False then
            oWindow.DisplayHeadings=True
        Else 
            oWindow.DisplayHeadings=False
        End If
End Sub

VBA code example

Sub DisplayHeadingsExampleToggle()
    'Bind widnow reference
    Dim oWindow As Window
    Set oWindow = ActiveWindow
    
    'Check for heading status and toggle
    If oWindow.DisplayHeadings = False Then
        DisplayHeadings = True
    Else
        DisplayHeadings = False
    End If
End Sub

Next >> Trace Dependents in Excel

Leave a Reply

Your email address will not be published.