Prevent Sheet rename without protecting structure Excel Addin C#

Prevent Sheet rename without protecting structure Excel Addin C#

There might be a case where you don’t want to allow user to change sheet name. In this article we will see how you can control the behavior and control over specific feature like sheet rename. There are couple of way user can rename the sheet:

  • Home Ribbon: under Home Ribbon >> Cells group >> Format drropdown >> Rename Sheet
  • Right click context menu: right click over sheet name and choose Rename Sheet command
  • Double Click: double click over sheet name itself

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

Home Tab Example:

Code example:

You need to change your ribbon.xml where you have to pass idMso of the Cells group and mark visible property to false as shown below:

<ribbon>
	<tabs>
		<tab idMso="TabHome">
			<group idMso="GroupCells" visible="false"/>
		</tab>
	</tabs>
</ribbon>

Let’s prevent right click context menu:

Code example:

public static void PreventSheetRename()
{
	Globals.ThisAddin.Application.CommandBars["Play"].Enabled = false;
}

Prevent user thorough Double click: This is little tricky hence required little code work. You need to define WorkSheet_Deactivate event and put a global variable which will hold existing sheet name. Then you have to validate the same variable on SheetSelection_Change event if your global variable value and Sheet selection change event value is different then user did a rename and you need to put sheet name back by taking string form global variables.

Protect: following steps will make sheet structure protected if you straight forward don’t want to apply above methods:

  1. Display the Review tab of the ribbon
  2. Click the Protect Workbook tool in the Changes group (Protect group if you are using Excel 2016 or a later version)
  3. Make sure that the Structure check box is selected
  4. Enter a password in the Password box
  5. Click on OK
  6. Reenter the password and click on OK

Step-1: Enter Password and make sure Structure checkbox is on

Step-2: Confirm password

Step-3: Try double clicking on sheet tab

Leave a Reply

Your email address will not be published.