Filter on sheet with some cells protected?

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
 
P

Pete_UK

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
 
R

Roger Govier

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.
 
K

Kirstie Adam

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...
 
R

Roger Govier

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
 
K

Kirstie Adam

Should the protection be switched on or off on the sheet before i put this
code in?

Kirstie
 
K

Kirstie Adam

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
 
G

Gord Dibben

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.
 
K

Kirstie Adam

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...
 
Top