Log In
Register
Home
July 7, 2016
Offline
I have the following code assigned to an object:
ActiveSheet.Unprotect Password:="EIS 2017"
Selection.EntireColumn.Hidden = False
Range("L5").Select
ActiveSheet.Protect AllowSorting:=True, AllowFiltering:=True
ActiveSheet.Protect Password:="EIS 2017"
End Sub
This works fine, but when I enable protection, the only options I want to be selected are Sort and Use Autofilter, nothing else. The macro above clears every checkbox in the Worksheet Protection dialog box upon execution. What is wrong here?
Paul
See Answer
Trusted Members
December 20, 2019
Offline
Sorting
I think that you can only sort on unprotected cells, which is kind of logical as you are changing them - although not really helpfull in this situation.
Autofilter
I dont think you can turn on the autofilter with protection, but you can allow the user to use the auto filter if it is already there
You also need all the protection options on one row otherwise the second one will over write the first one.
Sub Macro31()
'
' Macro31 Macro
'
'ActiveSheet.Unprotect
'
ActiveSheet.Unprotect Password:="EIS 2017"
Range("G5:L5").Select
If ActiveSheet.AutoFilterMode = True Then ActiveSheet.AutoFilterMode = False
Range("d11:f11").AutoFilter
Selection.EntireColumn.Hidden = False
Range("L5").Select
ActiveSheet.Protect Password:="EIS 2017", AllowSorting:=True, AllowFiltering:=True
End Sub
As an option, you could add a button to the worksheet which would let the VBA do the sorting (see attached). Not sure on your sheet if this is fesible but could be an option
Answers Post
1 Guest(s)
