Auto filters and locked protected sheet

[wmtsub]

Is ther some way to protect a worsheet so ppl can not write/delete data but still can use the colum filters to sort and search?

 

[kpasa]

Is ther some way to protect a worsheet so ppl can not write/delete data but still can use the colum filters to sort and search?

Under the Review tab, select Protect sheet. From there, check the following boxes:

• Select Locked Cells
• Select Unlocked Cells
• Sort
• Use AutoFilter

The VBA equivalent is

ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowSorting:=True, AllowFiltering:=True

 

[wmtsub]

Thank you. Had the 1st part it was via vba I could not get to work.

 

[wmtsub]

Ran into an issue, tried to apply a password as well but cant fiond the syntax. can you help?

ActiveSheet.Protect "*** DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowSorting:=True, AllowFiltering:=True

 

[wmtsub]

Also I can choose a filter and enter search criteria just finme with this script. But it will not let me sort alphabetically from teh filter.

 

[kpasa]

The quick answer is to record a macro doing exactly what you need to do. The return recording is usually bulky, but it gives you a good direction of how to do filtering, sorting, and viewing from a macro sub. Plus it gives you the benefit of learning some basics of how to handle excel objects from a sub (not called a script)

 

[wmtsub]

thanks, that I know and we are past that. not sure you saw the path of the thread....

 

[wmtsub]

I worked out the syntax for the macro. So now I only have the issue where I can not sort the protected sheet via the filter. Any ideas?

 

[wmtsub]

My mistake I did not correct the syntax. How do I add a password to this?

ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowSorting:=True, AllowFiltering:=True​

 

[wmtsub]

ok so I did find the syntax. Seems when I copied it typo'd. Sorry.