How to protect only certain ranges in a sheet using VBA?

Protecting specific ranges in an Excel sheet using VBA involves allowing users to edit certain cells while the rest of the sheet is protected. Here’s a step-by-step guide on how to achieve this:

Unprotect the Sheet (if it’s already protected):

Before you start, make sure your sheet is unprotected, as you’ll need to change some cell properties. You can manually unprotect a sheet by going to the “Review” tab in Excel and selecting “Unprotect Sheet.”

Unlock the Cells in the Desired Range:

Select the range of cells that you want users to be able to edit.
Right-click and choose “Format Cells.”
Go to the “Protection” tab and uncheck “Locked.”

Write a VBA Script:

You’ll create a macro to protect the sheet while allowing certain ranges to be editable.

Here is an example VBA code snippet:

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

       ' First, unprotect the sheet if it's already protected
       ws.Unprotect Password:="yourpassword" ' Optional password

       ' Lock all cells on the sheet
       ws.Cells.Locked = True

       ' Unlock cells in the specified range
       With ws.Range("A1:B10") ' Change to your desired range
           .Locked = False
       End With

       ' Re-protect the sheet, allowing certain actions
       ws.Protect Password:="yourpassword", UserInterfaceOnly:=True, AllowFormattingCells:=True, AllowFormattingColumns:=True, AllowFormattingRows:=True
        
       ' Alternatively, you can customize allowed actions per your requirement, such as:
       ' AllowSorting:=True, AllowInsertingRows:=True, AllowInsertingColumns:=True, etc.

   End Sub

Run the Macro:

Open the VBA editor by pressing `ALT + F11`.
Insert a new module by right-clicking on your VBA project and selecting Insert > Module.
Paste the above code into the module window.
Modify the code to replace `”Sheet1″` with your sheet’s actual name and update the range `”A1:B10″` to the range you want to unlock.
Close the VBA editor.
Run the macro by pressing `ALT + F8`, selecting `ProtectSheetWithExceptions`, and clicking “Run.”

Fine-Tune Protections:

The `ws.Protect` method has several optional parameters allowing you to specify which actions users can perform. Adjust these according to what you want to allow on your sheet (e.g., `AllowSorting`, `AllowFiltering`, etc.).

This approach ensures that users can input data or modify only in specifically designated ranges, while the rest of the worksheet is protected against accidental changes. Make sure to store your password securely if you decide to use one for protection.

facebooklinkedin