Excel VBA: Protect a Sheet and Allow Filter and Sort

Yes. In Excel VBA, protect the worksheet with AllowFiltering:=True and AllowSorting:=True. The filter controls must already exist, and sorting a protected sheet generally requires every cell in the sort range to be unlocked; allowing sorting does not by itself let users rearrange locked cells.

What the protection options allow

Worksheet protection is designed to stop changes to protected content while leaving selected actions available. The VBA Worksheet.Protect method has separate options for filtering and sorting. Setting AllowFiltering to True lets users change the criteria in an existing AutoFilter. Setting AllowSorting to True permits sorting when Excel’s requirements for the sort range are met.

These options do not mean “allow all data actions.” Filtering hides rows that do not match the chosen criteria; it does not change the underlying values. Sorting changes the order of records, so Excel applies stricter rules when the sheet is protected. Neither option unlocks cells or grants permission to edit protected content.

Protection is also not the same as encrypting a workbook or securing sensitive data. It is a worksheet-level editing restriction. Use file encryption and appropriate access controls if the goal is to keep confidential information from unauthorized readers.

Prepare the sheet before protecting it

Set up the filter before applying protection. On Windows, select a cell in the data range and choose Data > Filter, or create an Excel table with Insert > Table. A table includes filter buttons in its header row and usually expands as records are added. On a protected sheet, users can use existing filter buttons, but they cannot turn AutoFilter on or off.

Decide which cells users should be able to edit. By default, worksheet cells are marked as locked, but that setting only takes effect when the sheet is protected. To permit edits in designated input cells, select them, press Ctrl+1, open the Protection tab, clear Locked, and choose OK. On Excel for Mac, use Command+1 to open Format Cells. Then protect the sheet. Users can edit the unlocked cells while the other cells remain protected.

Before you run the macro, save a copy of the workbook. VBA macros cannot be undone with Ctrl+Z. Also save the workbook as a macro-enabled file, such as an .xlsm workbook, if you need the code to remain available.

VBA code to protect a sheet and allow both actions

This example assumes the worksheet is named Data and that its filter has already been applied. Change the sheet name and replace the sample password with one appropriate for your workbook. Put the procedure in a standard VBA module, such as Module1.

Sub ProtectDataSheet()
    Dim ws As Worksheet

    ' Use the worksheet that already has its filter controls.
    Set ws = ThisWorkbook.Worksheets("Data")

    ' Protect the sheet while allowing filtering and eligible sorting.
    ws.Protect Password:="ChangeThisPassword", _
        DrawingObjects:=True, Contents:=True, Scenarios:=True, _
        UserInterfaceOnly:=True, AllowFiltering:=True, AllowSorting:=True
End Sub

To insert the code, press Alt+F11 on Windows to open the Visual Basic Editor, choose Insert > Module, and paste the procedure. On Mac, open the Visual Basic Editor from the Developer tab or Tools > Macro > Visual Basic Editor, then insert a module. If the Developer tab is hidden, enable it in Excel’s ribbon settings. Run the procedure from the editor or the Macros dialog.

The named arguments make the intent clear: drawing objects, cell contents, and scenarios remain protected, while the two specified actions are permitted subject to Excel’s worksheet rules. If you omit the Password argument, the sheet can be protected without a password, but that is easier for someone to unprotect from the interface.

Why sorting may still fail

The most common surprise is that AllowSorting:=True is not enough when the sort range contains locked cells. Excel’s protected-sheet rules generally require all cells in the range being sorted to be unlocked. If the data table is locked to prevent edits, users may see a message that the operation cannot be completed even though the VBA option is present.

There is a practical trade-off: unlocking the whole table can let users edit its values as well as sort it. If users must not change the records, do not unlock the data merely to make manual sorting work. Instead, consider a controlled macro that temporarily unprotects the worksheet, performs a specified sort, and protects it again. That approach needs careful error handling so the sheet is not accidentally left unprotected if the sort fails. It also requires macros to be enabled.

For a small input area, a different layout may help: keep the source table protected and let users sort a separate, unlocked report or a copy of the data. The right choice depends on whether edit prevention or interactive sorting matters more. Test the exact range, table structure, and protection settings in a copy before distributing the workbook.

Filtering on a protected worksheet

AllowFiltering:=True applies to an AutoFilter that is already in place. Users can open its dropdowns and change the criteria, for example to show only rows where Status is “Open” or Region is “West.” They do not need the filtered cells to be unlocked just to change the filter criteria.

They cannot use this setting to add or remove AutoFilter controls while the sheet is protected. Set up the filter first, then protect the sheet. If users need to change the columns or range covered by the filter, a workbook owner can unprotect the sheet, adjust the filter, and protect it again. Tables are often convenient for datasets that grow because their filter buttons are part of the table header.

Filtering is not a security boundary. A user who can access the workbook may be able to reveal hidden rows by changing the filter, or may have other ways to inspect the data. Do not use a filtered view to conceal information that users are not authorized to see.

What UserInterfaceOnly changes

The example sets UserInterfaceOnly:=True. This tells Excel to enforce protection for actions made through the user interface while allowing VBA code to make changes to protected cells. It can be useful when a macro needs to update formulas, refresh a report, or write a timestamp without asking users to unprotect the sheet.

This setting does not give worksheet users extra permissions, and it does not automatically make a macro safe. It only affects how protection applies to VBA operations. Excel does not retain the UserInterfaceOnly setting when the workbook is closed, so code that depends on it should apply protection again whenever the workbook opens. Put an open event procedure in the ThisWorkbook module:

Private Sub Workbook_Open()
    Dim ws As Worksheet

    ' Reapply protection settings for this Excel session.
    Set ws = ThisWorkbook.Worksheets("Data")
    ws.Protect Password:="ChangeThisPassword", _
        DrawingObjects:=True, Contents:=True, Scenarios:=True, _
        UserInterfaceOnly:=True, AllowFiltering:=True, AllowSorting:=True
End Sub

Use the same password as the protection setup, and keep the workbook in a macro-enabled format. The event runs only when macros are enabled. If macros are disabled, the saved worksheet protection still applies, but the interface-only behavior and any VBA automation will not run.

Common issues and sensible checks

Filter arrows are missing: Apply AutoFilter or create the table before protecting the sheet. AllowFiltering does not add the controls to a protected sheet.
Filtering works but sorting does not: Check whether every cell in the intended sort range is unlocked. Also confirm the sort range is the one Excel is actually trying to sort.
Users can edit too much: Review which cells have Locked cleared. Unlocked cells are editable when the sheet is protected; they are not just sortable cells.
A macro cannot update protected cells: Apply protection with UserInterfaceOnly set to True for the current session, and reapply it in Workbook_Open if needed.
The sheet stays protected after a macro error: If a macro temporarily unprotects the sheet, structure it to restore protection even when an operation fails. Test that workflow on a copy.
The password is treated as strong security: Worksheet protection is intended to control ordinary editing, not provide robust confidentiality. Use workbook encryption where protecting access to the file is required.

On Excel for the web, VBA macros cannot be created or run, so this automation needs desktop Excel. Macros and worksheet protection are available in desktop Excel for Mac, although the Visual Basic Editor entry points and keyboard shortcuts differ from Windows. The AllowFiltering and AllowSorting arguments are VBA worksheet options; they do not change the general protection limits described above.

Frequently Asked Questions

Can users filter a protected Excel sheet?
Yes, if AutoFilter is already applied and the sheet was protected with AllowFiltering:=True. Users can change filter criteria, but they cannot use that permission to add or remove the filter controls on the protected sheet.

Why can I filter but not sort a protected sheet?
Filtering and sorting have different protection requirements. Sorting commonly fails when cells in the range are locked. AllowSorting permits sorting only when Excel’s requirements for the selected range are met; it does not unlock that range.

Does AllowSorting let users edit the cells?
No. It enables an allowed sorting action, not general editing. However, unlocking cells to satisfy the sorting requirement also makes those cells editable while the sheet is protected, so check the trade-off before changing their Locked setting.

Does UserInterfaceOnly stay enabled after closing Excel?
No. Excel does not save that setting for the next session. Reapply protection with UserInterfaceOnly set to True, commonly from the Workbook_Open event, each time the workbook opens.

Can I use this VBA code in Excel for the web?
No. Excel for the web does not run VBA macros. Use desktop Excel for Windows or Mac to run the code; the web version can work with many protected workbooks, but it cannot perform this VBA setup.

Choosing the right setup

For a typical shared list, create a table, apply protection with filtering and sorting allowed, and test the result using the same kinds of cells and ranges users will handle. If sorting succeeds only after unlocking the entire dataset, decide whether the resulting edit access is acceptable. If not, use a controlled VBA operation or a separate report view rather than weakening protection across the source records.

Keep the permission model simple: unlock only intended input cells, apply filter controls before protection, and explain to users which actions are available. A short real-user test can reveal whether the worksheet design matches the work people need to do without exposing cells to accidental edits.

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.