• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Macro to Select Protection Options

I have the following code assigned to an object:
>>> use code - tags <<<
Sub Picture877_Click()
' Picture877_Click Macro
    ActiveSheet.Unprotect Password:="EIS 2017"
    Selection.EntireColumn.Hidden = 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. If I first set these manually, the macro above clears all the checkboxes. So I tried recording a macro in which I selected only the two options I want, but still, it cleared every checkbox upon execution. Something is causing this reset but I can't figure out why.

Can you help with the proper code to be inserted into the above?

Last edited by a moderator:
Thanks. I modified it like this:

>>> use code - tags <<<
ActiveSheet.Unprotect Password:="EIS 2017"
    Selection.EntireColumn.Hidden = False
    ActiveSheet.Protect AllowSorting:=True, AllowFiltering:=True
    ActiveSheet.Protect Password:="EIS 2017"
End Sub
But when I verify the protection settings, every checkbox is still cleared. What is wrong here?

Last edited by a moderator:


Excel Ninja
a) the 2nd time : Use code - tags
b) I cannot see any checkboxes, cannot comment.
c) You seems to do two times protection
... would the 2nd one 'overwrite' then the 1st one?
... and that's why only the 2nd one works.
Never tested...


Excel Ninja
Do You mean those checkboxes?
... Excel has many many checkboxes.
Maybe something like this ..
> here case Sheet2 - okay?

Screenshot 2020-10-12 at 19.24.53.png

Below same ... do You notice difference?
= Excel cannot let You use all options
... because
... ... check the last snapshot.
Screenshot 2020-10-12 at 19.25.14.png

Now, possible to use Sort & Filter too
Screenshot 2020-10-12 at 19.25.26.png

Is this something what You tried to ... explain
if not, then You should give more hints, please.
Your version of Excel looks different than mine (Office 365), but it's the same idea. I want only the Sort and Use Autofilter checkboxes checked, everything else cleared. This snapshot has everything cleared but those two. 71588


Excel Ninja
... so what is missing or extra?
Your settings seems also protect contents of locked cells.
Can You see those setting then worksheet is protected? ... I cannot!


Excel Ninja
Did You check my sent 2nd and 3rd snapshots?
Your Sheet1 situation was like 2nd snapshot
Your Sheet2 situation was like 1st snapshot.
... after ...
left top checked boxes
it was possible to select as You would like to do.
I do not have any idea of Your Excel version, but
a) there could be something same
b) there could be some kind of 'lock' ( those are not ... so so ... nice )
Test these:
> Compare Your version with Sheet2
> Try to run that macro to Sheet2 and compare
> Try to run that with new Excel-file
>> test this Your file again ...


Last edited:
In Ofice 365, you can select only Sort and Autofilter, unlike your version of Excel.
The reason I want to disable selecting cells is that I'm working with a sortable table that I don't want users to wreck. If I protect the worksheet with just Sort and Autofilter, it allows them to use the sort table features but not select any cells in the table. That'll keep them from messing it up.


Excel Ninja
Your file has Sheet1, Sheet2 & Sheet3 - okay?
You have 'played' with protection with Sheet1 only - okay?
... then Sheet2 should be as normal situation - okay?
... to which attachment do You refer?
Some times - then something not work - anything could go like 'lock' - even Excel won't know what to do/have!
You(r Excel version) seems to have below
Screenshot 2020-10-12 at 21.56.30.png
instead my version, which You can see from those my snapshots.
My version needs to have at least one of left top corner checkbox to be selected.
... if not then situation is same as the 2nd snapshot - many grey ( not possible ) not possible to use checkboxes.
There could be other differences between Excel-versions.
I have some kind of image - how do my Excel-version works.
I know that Your Excel version won't work with my computer.

>> Did You test that my sent Your file?
... do it work same way as Your original that file?
And yet, having only sort and filter capabilities checked works exactly the way I want it to. Users can sort and filter, but they can't select a cell and change it.