Excel - having different permissions for different ranges on one tab

Question

I am trying to have on one a tab a section where users cannot edit or modify the cells in any way (only point and click), and then another section where they can only paste/copy/delete entries with the cells (but not change the formatting). I do not see how to assign different permissions to different ranges.

Thanks for the help.

Answer 1

This isn't possible using Excel's built in protection. You can have a range that is unprotected where the rest of the worksheet is protected, but you can't different degrees of protection across different ranges.

You could do some tricky work in VBA to fire different worksheet protection options based off of the cell that is clicked using the worksheet_selectionchange() event. Essentially you would detect what target range was clicked, unprotect the sheet, then reprotect the sheet with whatever set of options you want for that particular cell/range that was selected.

Something like:

Private Sub Worksheet_Change(ByVal Target As Range)
    'Detect if cells A1:B10 was clicked, in this case I want them to be able to copy/paste, but not format or anything else
    If Not (Intersect(Target, Me.Range("A1:B10")) Is Nothing) Then
        Me.Unprotect
        Me.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True

    'If it's C1:D10 then allow formatting, but nothing else
    ElseIf Not (Intersect(Target, Me.Range("C1:D10")) Is Nothing) Then
        Me.Unprotect
        Me.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowFormattingCells:=True

    End If
End Sub

That would go into the worksheet's vba, not a seperate module. You can specify passwords in the Protect and Unprotect methods as well, if you go this route. This isn't a 100% great option since it relies on VBA and constantly protecting and unprotecting the sheet as the user clicks around, but if you need different restrictions for different ranges on the same worksheet, then this is probably your best bet.