Allow Edit Ranges in Protected Sheet Excel Addin

Protect

Excel serves great security while dealing with data. There are many ways to protect your sensitive information from outsiders or unintended users like Deep Hidden Sheet, Protect Sheet, Lock cells etc. Consider a scenario where you are dealing with sensitive information which you don’t want to allow user to be edited except you want.

Protect Sheet is the right fit for this use case but it protects entire sheet. Now you left with nothing either share password with users to make edit or don’t protect sheet at all. Excel offers Allow Edit Ranges feature which creates exception for Protect Sheet method. Let’s see how we can achieve allow edit range operation:

Steps

1. Navigate Review tab
2. Select Allow Edit Ranges from Protect group (figure 1.0)
3. Click New button from the dialog a new popup comes (figure 1.1)
4. Followings are prompted:
   • Title: title of the range to be allowed when password prompts
   • Refers to cells: put the range which you would like to allow
   • Range Password: put password to unlock selected range
   • Permissions: if you wish to add window authentication you can opt for Permission button and it will popup with windows user permission add dialog where you can search respective user in your Windows directory.
5. Click OK and a password reconfirm dialog appears where you need to supply exact same password that you have given in figure 1.o
6. Click OK and you will be back to the figure 1.0
7. Paste permissions information into a new workbook if you check it will paste all permissions into an new workbook keep it uncheck for now
8. Click on Protect Sheet button
9. A dialog appears (figure 1.1)
10. Put password and check appropriate protections based on your need, Ideally put a different password
11. Click OK and a password reconfirmation dialog appears (figure 1.3)
12. Click OK
13. That’s it now its time to check what you have done is upto your expectations? Try to delete contents from the range you selected under Point 4 (Refers to cell) above

When you try to make changes in Allow Edit Ranges area it prompts for the password which you supplied at Point 4. Put the password and you are good.

Let’s do some code around to enable Allow Edit Ranges feature. Note: I am not going to write code for basic sheet protection as I have given a complete detail about Protection in Excel in my previous article which you can refer here.

C# code example

private void btnAllowEditRanges_Click(object sender, RibbonControlEventArgs e)
{
	//Get Sheet reference
	excel.Worksheet oWorksheet = Globals.ThisAddIn.Application.ActiveSheet;

	//Retains user selection to be allowed
	excel.Range oRange = Globals.ThisAddIn.Application.Selection;

	//Allow Edit Ranges
	oWorksheet.Protection.AllowEditRanges.Add("VBAOVERALL Allow Edit Range", oRange, "12345");

	//Protect sheet
	oWorksheet.Protect("234",true,true,true);
}

VB.Net code example

Private Sub AllowEditRanges_Click(sender As Object, e As RibbonControlEventArgs) Handles AllowEditRanges.Click
	'Get Sheet reference
	Dim oWorksheet as excel.Worksheet
	oWorksheet = Globals.ThisAddIn.Application.ActiveSheet

	'Retains user selection to be allowed
	dim oRange as excel.Range
	oRange = Globals.ThisAddIn.Application.Selection

	'Allow Edit Ranges
	oWorksheet.Protection.AllowEditRanges.Add("VBAOVERALL Allow Edit Range", oRange, "12345")

	'Protect sheet
	oWorksheet.Protect("234",true,true,true)
End Sub

VBA code example

Public Sub AllowEditRangeExample()
    'Get Sheet reference
    Dim oWorksheet As Excel.Worksheet
    Set oWorksheet = ActiveSheet

    'Retains user selection to be allowed
    Dim oRange As Excel.Range
    Set oRange = Selection

    'Allow Edit Ranges
    oWorksheet.Protection.AllowEditRanges.Add "VBAOVERALL Allow Edit Range", oRange, "12345"

    'Protect sheet
    oWorksheet.Protect "234", True, True, True
End Sub

Thank you for visiting vbaoverall.com you can learn more by subscribing our portal. Next >> Content Controls in Word Document VBA code

Share on Facebook

Tweet