Filter on sheet with some cells protected?

  • Thread starter Thread starter Kirstie Adam
  • Start date Start date
K

Kirstie Adam

Hi all,

I have a spreadsheet which has four columns of formulas protected so people
can't change them!

However, i still want people to be able to filter the spreadsheet and this
isn't working, is there a way of locking those cells but not stopping people
from filtering?

Kirstie
 
If you are using Excel 2000 or earlier, then you do not have the
option to allow filtering when you protect a sheet. However, one way
around this is to copy/move your formulae to another sheet which you
can protect (and hide if you want to), and then have links in your
first sheet to the cells with the formulae in. Then you don't need to
protect that sheet to avoid overwriting the formulae.

Hope this helps.

Pete
 
Hi Kirstie

You don't say which version of XL you are using.
From XL2002 onward, when you choose to protect a sheet, there is an option
to allow users to use Autofilter.
Just scroll down the dialogue box that appears when you use Tools>Protect.

Post back if you need a VBA code solution.
 
It is excel 2000 i am using.


Roger Govier said:
Hi Kirstie

You don't say which version of XL you are using.
From XL2002 onward, when you choose to protect a sheet, there is an option
to allow users to use Autofilter.
Just scroll down the dialogue box that appears when you use Tools>Protect.

Post back if you need a VBA code solution.
Click to expand...
 
Hi Kirstie

In that case you will need to add some code to your workbook, to enable the
facility.

In case you missed Gord's posting, (he pointed to where you can find the
code along with instructions on how to implement it) I repeat the link below
http://www.contextures.on.ca/xlautofilter03.html#Protect


You will need to amend the line
With Worksheets("Data")
to be whatever your sheet name is in place of the word Data
 
Should the protection be switched on or off on the sheet before i put this
code in?

Kirstie
 
I can't get this to work!
I use (a little) VB in Access, but have not used it in excel before.

Should i also be changing the range in

Range("A1")

to the range my sheet takes up?

and


Password="password"

should that become the password i use for protecting and unprotecting the
sheet?

Sorry, complete newbie at this!

Kirstie
 
Kirstie

See in-line responses

I can't get this to work!
I use (a little) VB in Access, but have not used it in excel before.

Should i also be changing the range in
Click to expand...

The Range("A1") sets the autofilter to column A

Change the "A1" to whatever column you want to filter on.
Range("A1")

to the range my sheet takes up?

and


Password="password"
Click to expand...

"password" is whatever password you place on the sheet when protecting it.

Could be "kirstie" or "drowssap".........................your choice.
 
excellent, got it working, thanks!

kirstie

Gord Dibben said:
Kirstie

See in-line responses



The Range("A1") sets the autofilter to column A

Change the "A1" to whatever column you want to filter on.

"password" is whatever password you place on the sheet when protecting it.

Could be "kirstie" or "drowssap".........................your choice.
Click to expand...
 
Back
Top