How to protect a sheet and allow certain actions using VBA?

To protect an Excel sheet using VBA while allowing certain actions, you can use the `Protect` method with specific arguments to enable the desired exceptions. This functionality is useful when you want to prevent users from making unwanted changes while still allowing them to perform specific tasks like sorting, filtering, or entering data in certain cells.

Here is a basic guide on how to use VBA to achieve this:

Sub ProtectSheetWithOptions()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1") ' Change "Sheet1" to your sheet name

    ws.Unprotect Password:="yourpassword" ' Use if the sheet is already protected

    ' Protect the sheet with specified options
    ws.Protect Password:="yourpassword", _
                AllowFormattingCells:=False, _
                AllowFormattingColumns:=False, _
                AllowFormattingRows:=False, _
                AllowInsertingColumns:=False, _
                AllowInsertingRows:=False, _
                AllowInsertingHyperlinks:=False, _
                AllowDeletingColumns:=False, _
                AllowDeletingRows:=False, _
                AllowSorting:=True, _
                AllowFiltering:=True, _
                AllowUsingPivotTables:=False
    
    ' Optional: Allow users to select locked and unlocked cells
    ws.EnableSelection = xlUnlockedCells ' Or xlNoRestrictions
    
    MsgBox "Sheet Protected with specific permissions."
End Sub

Open the VBA Editor:

Press `ALT` + `F11` to open the VBA editor in Excel.

Insert a Module:

Right-click on any of the items in the `Project Explorer` pane.
Click on `Insert` > `Module`.

Write the VBA Code:

Copy and paste the following VBA code into the module:

Explanation of VBA Code:

Protect Method Options: These are passed as arguments to the `Protect` method to enable or disable certain capabilities while the sheet is protected.

`AllowSorting` and `AllowFiltering` are set to `True`, allowing users to sort and filter data.
You can adjust other parameters (`AllowFormattingCells`, `AllowInsertingColumns`, etc.) as needed.

Password: The sheet is protected with a password (`”yourpassword”`). Change it to your desired password.
EnableSelection: This property determines which cells users can select. Options include:

`xlUnlockedCells`: Only unlocked cells can be selected.
`xlNoRestrictions`: All cells can be selected.

Running the Macro:

Press `F5` or go to `Run > Run Sub/UserForm` to execute the macro.

Testing:

Go to Excel, try to perform actions, and check the allowed and restricted functionalities.

This script gives you a simple yet flexible way to protect your worksheet while still permitting specific user actions as per your requirements. Customize the permissions as needed by modifying the options in the `Protect` method.

facebooklinkedin