Allow VBA code inside protected worksheet

Question

I recorded the below VBA code that hide/unhide rows then sort on worksheet_Activate event, in no time :). Just one thing, the worksheet is required to be protected and this code refuse to be executed in a protected sheet and got this error message :

"run-time error 1004 "You cannot use this commend on a protected sheet..."

Could you please advise how we can protect the sheet and allow this code at the same time?

Sub HIDESORT()
    ActiveSheet.Range("$A$10:$K$500").AutoFilter Field:=7, Criteria1:=">0", _
        Operator:=xlAnd
    ActiveWorkbook.Worksheets("Control Implementation Plan").AutoFilter.Sort. _
        SortFields.Clear
    ActiveWorkbook.Worksheets("Control Implementation Plan").AutoFilter.Sort. _
        SortFields.Add Key:=Range("G10:G500"), SortOn:=xlSortOnValues, Order:= _
        xlDescending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Control Implementation Plan").AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

when you protect the worksheet you've to allow users the possibility to hide/unhide cells by selecting the checkbox allow format columns. Try adding this segment to your code ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowFormattingColumns:=True — psychicebola

Excel Developers Excel Developers · Accepted Answer · 2015-08-11T14:08:13

1

votes

ActiveSheet.Protect UserInterfaceOnly:=True

Allow VBA code inside protected worksheet

2 Answers