How to Lock and Protect Specific Cells in Excel While Allowing Filtering and Column Adjustments by using VBA

Problem statement: 

In a folder, you have 40 excel templates where users should not able to edit header and some fields of the template. some fields should able to edit and can apply filters and able to change the width of the columns. please refer the below screenshot.

How to Lock and Protect Specific Cells in Excel While Allowing Filtering and Column Adjustments 

Excel is a versatile tool for data management, but protecting sensitive information while allowing users to interact with other parts of the sheet can be challenging. In this post, we’ll explore how to lock specific cells in Excel to make them uneditable, while enabling users to filter data and adjust column widths. We’ll also automate this task for multiple Excel files in a folder using VBA (Visual Basic for Applications).

Use Case

Imagine you have multiple Excel files containing sensitive data in the range A1:P that must be protected from edits. However, users still need the ability to:

• Filter data in the protected range.

• Adjust column widths to improve visibility.

• Edit specific ranges (e.g., P5:AV) without restrictions.

Manually setting these permissions for each file can be time-consuming. VBA macros offer a powerful way to automate this task.

The VBA Macro Solution

Below is a VBA macro that locks the specified ranges, allows filtering and column width adjustments, and processes all Excel files in a chosen folder.

The VBA Code: 

Sub ProtectCellsInFolder()

    Dim folderPath As String

    Dim fileName As String

    Dim wb As Workbook

    Dim ws As Worksheet

    ' Specify the folder path containing the Excel files

    folderPath = "C:\YourFolderPath\" ' Change this to your folder path

    If Right(folderPath, 1) <> "\" Then folderPath = folderPath & "\"

    ' Loop through each Excel file in the folder

    fileName = Dir(folderPath & "*.xlsx")

    Do While fileName <> ""

        ' Open the workbook

        Set wb = Workbooks.Open(folderPath & fileName)

        ' Loop through each worksheet in the workbook

        For Each ws In wb.Worksheets

            ' Unprotect the worksheet in case it's already protected

            ws.Unprotect Password:="YourPassword"

            ' Unlock all cells to reset their locked state

            ws.Cells.Locked = False

            ws.Cells.FormulaHidden = False

            ' Lock the range A1:P to prevent editing, but allow column adjustments

            ws.Range("A1:P" & ws.Rows.Count).Locked = True

            ws.Range("A1:P" & ws.Rows.Count).FormulaHidden = False

            ' Ensure the range P5:AV is editable

            ws.Range("P5:AV" & ws.Rows.Count).Locked = False

            ' Allow filtering

            If ws.AutoFilterMode = False Then

                ws.Range("A1").AutoFilter

            End If

            ' Protect the worksheet with settings to allow filtering and column width adjustments

            ws.Protect Password:="YourPassword", _

                       UserInterfaceOnly:=True, _

                       AllowFiltering:=True, _

                       AllowFormattingColumns:=True

        Next ws

        ' Save and close the workbook

        wb.Save

        wb.Close SaveChanges:=True

        ' Move to the next file

        fileName = Dir

    Loop

    MsgBox "Cells protected successfully in all files."

End Sub

How It Works

Folder Path: Specify the path to the folder containing your Excel files. The macro processes all .xlsx files in this folder.

Cell Locking:

All cells are unlocked initially to reset any prior protection.

The A1:P range is locked to prevent editing but allows filtering and column adjustments.

The P5:AV range is unlocked to remain editable.

Filtering: An AutoFilter is applied to A1 if not already present.

Worksheet Protection: Protection is applied with these permissions:

Allow Filtering: Users can apply and clear filters.

Allow Column Adjustments: Users can resize column widths in the locked range.

Iterating Through Files: The macro loops through each file in the folder, applies the settings to all worksheets, and saves the changes.

Step-by-Step Instructions

1. Prepare Your Environment

• Enable the Developer tab in Excel to access the VBA editor:

  • Go to File > Options > Customize Ribbon and check Developer.

2. Write the Macro

• Open the VBA editor with Alt + F11.

• Create a new module:

  • Go to Insert > Module and paste the VBA code above.

3. Set the Folder Path

• Replace C:\YourFolderPath\ in the code with the actual folder path containing your Excel files.

4. Run the Macro

• Press F5 or click Run in the VBA editor to execute the macro.

• The macro will process all .xlsx files in the specified folder, applying the protection settings.

Customizing the Macro

• Change Password: Replace "YourPassword" with your desired password to protect the sheets.

Support Additional File Types: Modify the Dir function to include .xlsm or .xls files:
vba
fileName = Dir(folderPath & "*.xls*")

Benefits of This Approach

• Automation: Save time by processing multiple files in one go.

• Selective Protection: Lock only the necessary cells while keeping other areas editable.

• Enhanced Usability: Allow users to interact with data (e.g., filtering, resizing) without compromising data integrity.