Protect sheet but hide rows?

[Guest]

Hello All,
I have a large spreadsheet that is used as a tool by many people, the
formula in the spreadsheet have to be protected to prevent them being
overwritten or deleted by users however I would like the users to be able to
hide or unhide rows/columns. When I use the protect worksheet function I
don't seem to be able to allow hide/unhide.
Any help very welcome!
Thanks

 

[Guest]

If the hiding and unhiding is to allow people to just see information that
pertains to them, the AutoFilter feature might work better for you.

 

[Guest]

What version of Excel are you using. In Excel 2003 there is an option to
permit formatting Rows (and/or columns) which will permit the operations on a
protected sheet. Not sure if 2002 (XP) had that option or not (I believe it
did), but 2000 and earlier do not have it.
If these are always the same rows to be hidden/revealed, then you could do
it with two macros - either attached to a button on the sheet or not; one to
unprotect the sheet, unhide the rows, protect it again; the other to
unprotect, hide, protect again.

But I"m betting that ~*Amanda*~ has the better idea.

 

[Guest]

Thank you this option to allow formatting of rows/columns works perfectly!
(Auto filter is not applicable as the ablity to hide rows/columns is to
allow users to personalise their view rather than filter information.)

 

[ianb]

Quick question on this (sorry to hijack the discussion!).

will this unprotecting and then protecting again work if you have a password
used? obviously you don't want this to become common knowledge which I guess
could be the case if someone goes into the macro itself.

 

[Gord Dibben]

ActiveSheet.Unprotect Password:="justme"

do the things you do

ActiveSheet.Protect Password:="justme"

If you lock the priject for viewing, users cannot see the code ir the password.

Open VBE and right-click on your project/workbook and select
Properties>Protection>Lock project for viewing.

Give it a password then Save the workbook, close and re-open to see protection
is on that project.


Gord Dibben MS Excel MVP

 

[ianb]

Perfect, thanks for your help!

Ian

ActiveSheet.Unprotect Password:="justme"

do the things you do

ActiveSheet.Protect Password:="justme"

If you lock the priject for viewing, users cannot see the code ir the password.

Open VBE and right-click on your project/workbook and select
Properties>Protection>Lock project for viewing.

Give it a password then Save the workbook, close and re-open to see protection
is on that project.


Gord Dibben MS Excel MVP

 

[Gord Dibben]

Glad to help.

Gord

Perfect, thanks for your help!

Ian