Allow edit range permissions

[jacobs322]

Hi all,

I'd like to reach out for help regarding the feature "allow edit range" and "protect sheet". 

About the workbook:
It consists of several sheets available to anyone with access, then there are sheets which should be edited by only 1 person, the rest should be able to just view. It looks like this:

A1 - editable only by Person A

B2 - editable only by Person B

C3 - editable only by Person C

D4, E5, F6 - editable by everyone with access to the file

What I did so far:

I learnt about allow edit range, so I created range A (whole sheet A1), range B... and so on. I specified the respective people in ranges' permissions' for their respective sheets and protected the sheet from other edits. The result is that Person A, B, C can edit their respective sheets and everyone else is only able to perform actions specified with "protect sheet". 

The issue:

Even though it works in principle, there are a few non-negligible bottlenecks it is causing.

For instance - people with only view access to the protected sheets are not able to perform CTRL+A -> FILTERS -> CLEAR ALL (even though they are allowed to use AutoFilter) - which is something we tend to do quite often to avoid extracting partial data as there are a lot of filterable columns.

The second issue is people who are allowed to edit the protected sheets can only do so in a limited manner. For instance they are not able to add/delete rows, which is something they need to do regularly.

Is there a way to give the eligible people full access to the protected sheet via allow edit ranges?

[#2 · Accepted solution]

@jacobs322

Unfortunately, the "Allow Edit Ranges" feature in Excel does not provide a way to give users full editing access to protected sheets while still restricting access to others. When you protect a sheet in Excel, it limits the users' editing capabilities to only what you have allowed through the "Protect Sheet" settings, regardless of any "Allow Edit Ranges" that have been defined.

As a result, users who are allowed to edit specific ranges using "Allow Edit Ranges" will still be restricted by the protection settings applied to the sheet, and they won't have full access to all editing capabilities, such as adding/deleting rows or using certain functionalities like CTRL+A -> FILTERS -> CLEAR ALL.

To achieve the specific level of access you described (i.e., giving eligible users full editing access to their respective ranges while still protecting the sheet for others), you may need to consider alternative solutions, such as using Excel VBA (macros) or a combination of data validation and worksheet events.

For example, you could use VBA to handle specific user access and manipulate the protection settings based on the users' actions. This would require a more complex setup and knowledge of VBA programming.

Please note that any solution involving VBA may require additional security considerations, especially if the workbook will be shared among multiple users in a shared network environment or on the internet.

If you are comfortable with VBA programming and require such a custom solution, you can explore VBA code options to handle sheet protection based on user actions. Otherwise, you may need to reconsider the level of protection and access needed for your workbook, balancing between data security and user convenience. Since no one has answered it for at least one day or more, I entered your question in various AI’s. The text and the steps are the result of various AI's put together.

My answers are voluntary and without guarantee!

Hope this will help you.

[#3]

Hi, thank you for taking your time and curating a reply for me. I'll try to find a solution with macros :)

[#4]

@jacobs322 

Here is a general outline of how you can approach this:

1. Enable Developer Tab: Go to "File" > "Options" > "Customize Ribbon." Check the "Developer" option to enable the Developer tab on the Excel ribbon.
2. Open VBA Editor: Click on the "Developer" tab, then click "Visual Basic" to open the VBA Editor.
3. Write VBA Code: In the VBA Editor, you can create macros that handle the protection settings. For example, you can create macros that automatically protect or unprotect the sheet based on specific triggers, like workbook open, specific button clicks, or other events.

For example, you can use the "Workbook_Open" event to unprotect the sheet for users who need full editing access and protect it for others. You can use VBA code like this:

vba code:

Private Sub Workbook_Open()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("YourSheetName") ' Replace "YourSheetName" with the actual sheet name

    ' Check if the user is allowed to edit the sheet (based on some condition)
    If UserIsAllowedToEdit Then
        ' Unprotect the sheet with password (if required)
        ws.Unprotect "YourPassword"
    Else
        ' Protect the sheet with password (if required)
        ws.Protect "YourPassword", UserInterfaceOnly:=True
    End If
End Sub

1. UserIsAllowedToEdit: The "UserIsAllowedToEdit" condition should be based on some criteria that determine whether the user is allowed to edit the specific range. This could be based on the user's login, Active Directory group membership, or any other custom logic you require.
2. Additional Macros: You can also create macros that handle specific actions within the allowed ranges, like adding/deleting rows or applying certain functionalities. For example, you can add a button or use worksheet events to trigger these macros when needed.
3. Protecting/Unprotecting Specific Ranges: If you need to allow editing only in certain ranges, you can use the Range.Locked property to lock/unlock specific cells programmatically. For example:

vba code:

ws.Range("A1:B10").Locked = False ' Unlock the range A1:B10
ws.Range("C1:D10").Locked = True ' Lock the range C1:D10

Remember to also set the sheet's UserInterfaceOnly property to True when protecting the sheet to allow the macros to modify the locked cells without unprotecting the entire sheet:

Vba code:

ws.Protect "YourPassword", UserInterfaceOnly:=True

Please note that VBA solutions require some programming skills and additional security considerations. Be cautious about protecting sensitive data and test the macros thoroughly before deploying them in a production environment. The VBA solution provided should work in most versions of Microsoft Excel that support VBA programming. However, there are some versions of Excel or Excel-like applications where VBA may not be available or may have limited support, like Excel Online, Excel for Mac, Excel Mobile, Excel Viewer and Some alternative spreadsheet applications that claim to support Excel files may have limited or no support for VBA. The text and the steps are the result of various AI's put together.

My answers are voluntary and without guarantee!

I hope this helps!