VBA Coding to enable sorting but DISABLE filtering

[owstie]

Hello, I've trawled the net and found endless posts/replies about enabling filtering but disabling sorting, but couldn't find any about doing the opposite.

I've got the following code in the relevant sheet (which I want to be protected upon opening):

Private Sub Worksheet_Activate()
With Sheet4

ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowFormattingColumns:=True, AllowFormattingRows:=True, AllowSorting:= _
True, AllowFiltering:=True
End With
End Sub

This allows filtering, but if I try to sort A-Z (or Z-A) I get a message: "The cell or chart you are trying to change is on a protected sheet. To make changes, click Unprotect Sheet in the Review tab (you might need a password)."


If I change it to the following (which in my head should work):

Private Sub Worksheet_Activate()
With Sheet4

ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowFormattingColumns:=True, AllowFormattingRows:=True, AllowSorting:= _
True, AllowFiltering:=false
End With
End Sub

It just disables the filter buttons entirely.

So I have 2 problems. Firstly getting the sorting to work, and secondly disabling the ability to filter. Please help!

Thanks.

 

[Fishboy]

Hello, I've trawled the net and found endless posts/replies about enabling filtering but disabling sorting, but couldn't find any about doing the opposite.

I've got the following code in the relevant sheet (which I want to be protected upon opening):

Private Sub Worksheet_Activate()
With Sheet4

ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowFormattingColumns:=True, AllowFormattingRows:=True, AllowSorting:= _
True, AllowFiltering:=True
End With
End Sub

This allows filtering, but if I try to sort A-Z (or Z-A) I get a message: "The cell or chart you are trying to change is on a protected sheet. To make changes, click Unprotect Sheet in the Review tab (you might need a password)."


If I change it to the following (which in my head should work):

Private Sub Worksheet_Activate()
With Sheet4

ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowFormattingColumns:=True, AllowFormattingRows:=True, AllowSorting:= _
True, AllowFiltering:=false
End With
End Sub

It just disables the filter buttons entirely.

So I have 2 problems. Firstly getting the sorting to work, and secondly disabling the ability to filter. Please help!

Thanks.

Hi owstie, welcome to the boards.

From Microsoft's own Office support website: "Sorting can only be performed on unlocked or unprotected cells in a protected worksheet."

This means that if the cells you want to sort have the "Locked" property when the sheet is protected then sort will not work.