Allow user to autofilter sort but without right to edit anywhere in worksheet

[gauravkrgautam]

Good Morning All Seniors,


I am struggling with a new problem from last 2 days that i have a macro enabled workbook.

where on sheet1 a userform is made, and data on sheet2 & sheet3 get updated from sheet1 inputs.

now i want to protect sheets2 & sheet3 with rights of autofilter & sortin in it but user should not be allow to edit any cell below header.

I have also tried to do with target.row header event but failed to do so.

Header row is in row 4 in both sheets and data will be on row("5:5000").

please help me.

thanks in advance

 

[jeffreyweir]

Are you trying to do this via VBA? If so, VBA isn't needed.


First, unlock any cells that you want the user to be able to edit (using the 'Format Cell' dialog box that comes up when you push F1)


Then, when you protect the worksheet, tick the 'Use Autofilter' option. This allows users to filter and sort.


If this doesn't solve your problem, i suggest you post a link to a sample workbook.

 

[gauravkrgautam]

thanks Jeffreyweir,


please find below link

http://rapidshare.com/files/3860241898/Sample%20file.xlsm


i also tried to do above method but unable to do.

it seems i wrong in selecting check buttons.

 

[jeffreyweir]

I note that in your sample book, the autofilter wasn't yet turned on in sheets 2 and 3.


First you need to turn it on manually before you protect the book.

 

[gauravkrgautam]

Thanks Jeffreyweir,

but that's too not working as i am looking for.

user will not be able to sort the columns. and yes you are right after filtering and then protecting it working.

but still sorting not working.


many thanks in advance

 

[Smallman]

Hi gauravkgautam


I was answering a very similar problem earlier in the year here;


http://www.ozgrid.com/forum/showthread.php?t=178622&highlight=protect+sort


I am not sure what you are trying to do is possible. In XL the wording is misleading in the protect sheet options.


You can either give your users access to sort the data then they can edit the data or you can just let them filter. A workaround is to use vba to un-protect the sheet, perform a specified sort and re protect the sheet at the end of the procedure. My guess is this would be probably your preferred option.


Anyways this is my understanding of the problem. Hope this helps.


Take care


Smallman