can i use an autofilter in a protected worksheet

  • Thread starter Thread starter Jackie Hyden
  • Start date Start date
J

Jackie Hyden

Hi there

I have created a spreadsheet and locked certain cells so that users cannot
change the information apart from one column. I then want to be able to
filter the data using the editable column. Can this be done? Even using
the Allow Users to edit ranges (with a different password) I cant get the
filter to run when the workbook is protected.

Any help would be appreciated

thanks

Jackie
 
Hi
which Excel version are you using. I think starting with Excel 2002
this is an option in the protection dialog.
 
It's under the options in Tools|Protect|protect worksheet in xl2002+.

But if you protect the sheet in code, you can do it any version (assuming that
the filter has already been applied--arrows are visible):

This setting isn't remembered between closing/reopening the workbook. (So
Auto_open is a nice spot for it.)

Option Explicit
Sub auto_open()

Dim wks As Worksheet
Set wks = Worksheets("sheet1")

With wks
.Protect Password:="hi", userinterfaceonly:=True
.EnableAutoFilter = True
End With

End Sub
 
Many thanks for your quick responses, the sheet was created in 2000 i think,
but I am trying to edit it using 2003. I dont think I am up to writing
code, but I certainly could give it a go if nothing else works.
 
Thanks all

I have managed to fix it, without code not sure but i think that either

a) the fact that there was a merged cell in the column that i was trying to
leave unlocked and autofilter on

or

b) applying lock either side of the main column and then unlock to the main
column before protecting, rather than applying lock to all then unlocking
the main column

what do you think?

regards

Jackie
 
Back
Top