Protect Excel Worksheet with multiple protection variables

Question

Using Excel VBA, how do I lock worksheets with multiple different variables i.e. Allowing users to use Autofilter and the sort option.

I have gotten this far:

Worksheets("Sheet 1").Protect Password:="Password"

What do I write next to allow user to: Select un/locked cells Format columns/rows Sort Use Autofilter

Thank you,

Robin Mackenzie Robin Mackenzie · Accepted Answer · 2016-07-04T04:10:41

There are 16 parameters you can use with the Protect method of the worksheet. Here is a code sample based off this MSDN article:

Option Explicit

Sub LockSheet()

    Dim ws As Worksheet

    Set ws = ThisWorkbook.Worksheets(1)

    ws.Protect Password:="Foo", _
        DrawingObjects:=True, _
        Contents:=True, _
        Scenarios:=True, _
        UserInterfaceOnly:=True, _
        AllowFormattingCells:=True, _
        AllowFormattingColumns:=True, _
        AllowFormattingRows:=True, _
        AllowInsertingColumns:=True, _
        AllowInsertingRows:=True, _
        AllowInsertingHyperlinks:=True, _
        AllowDeletingColumns:=True, _
        AllowDeletingRows:=True, _
        AllowSorting:=True, _
        AllowFiltering:=True, _
        AllowUsingPivotTables:=True

End Sub

Protect Excel Worksheet with multiple protection variables

2 Answers