Dynamically hide/unhide sheets in Excel Addin C#

Dynamically hide/unhide sheets in Excel Addin C#

Hide/Unhide Sheet:

There are various methods to hide/unhide sheets in Excel but when it come to write code, it turns a bit tricky job as there are couple of usecases needs to be handled to make sure 100% working solution. In this article I will try to give you deep understanding to make a bug free code by handling various scenarios dynamically.

Prerequisites:

  • Visual Studio 2015 or above having Microsoft Office for Developer Tool installed
  • Create Excel Addin in C# code style (Visual Studio Tools for Office)
  • Create a ribbon designer and put button

Code example:

private void HideUnhideWorkSheet(bool hideSheet)
{
	//get reference of active workbook
	excel.Workbook oWorkbook = Globals.ThisAddIn.Application.ActiveWorkbook;
	//take user input
	var sheetName=Globals.ThisAddIn.Application.InputBox("Enter Sheet Name:", "Hide/Unhide Sheet", string.Empty);
	Boolean checkSheetInput;
	//check if user has not dismiss InputBox by hitting Cancel button
	if(!Boolean.TryParse(sheetName.ToString(),out checkSheetInput))
	{
		//Check if sheet name supplied by user exists
		try
		{
			//binding sheet reference
			excel.Worksheet oWorksheet = oWorkbook.Worksheets[sheetName];
			//Check if sheet already hidden
			if (!hideSheet)
				oWorksheet.Visible = excel.XlSheetVisibility.xlSheetHidden;
			else
				oWorksheet.Visible = excel.XlSheetVisibility.xlSheetVisible;
			return;
		}
		catch (Exception ex)
		{
			MessageBox.Show(@"Sheet name does not exists");
			return;
		}
	}	
}

Output:

On click of OK Sheet6 would go in hidden mode. However the code I have provided will work in both cases hide/unhide. Please leave your comments in reply section.

Next : rename sheet in Excel C#

Leave a Reply

Your email address will not be published.