Allow edit range permissions

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!