Protect Multiple Worksheets at One time

[Thomas5000]

I was trying to protect multiple worksheets at a time. So i selected the tabs at the bottom and started unchecking the Locked check box after going to Format Cells. But what i found out was that it was unlocking the first sheet but not all the following sheets.

I have 40 worsheets in this book and numerous elements per sheet, how can i protect several sheets at once? Am I required to protect them one at a time?

All i wnat to do is prevent users from accidentally changing one of the formulas on the worksheets. It will throw off all the other calculations if the users mess up a formula since they are not smart enough to fix it.

Thanks!

 

[SkipVought]

Hi,

"... started unchecking the Locked check box ..."

You must do this BEFORE you protect a sheet.

The individual cells' locked property cannot be changed in a Sheet Group, whereas the Protect property can be applied to a group of sheets.

Skip,
[sub]
To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white] [/sub]

 

[Thomas5000]

I found this article on the Internet.

http://support.microsoft.com/kb/142111

But how do i lock and unlock cells on multiple sheets. I'm more concerned about this. Even if i can protect the sheets it matters little if people can't enter any information on the sheets at all.

The whole purpose of locking and unlocking is to discriminate between which cells you want protected and which you want users to alter. Well, i have 40 sheets and about 12 important sections per sheet to protect. That comes to about 480 sections in a workbook. Not very practical.

It would seem to me that this should be done the same way you do any ohter formatting even if they didn't allow you to protect sheets all at once, this would be a helpful feature.

Thanks. So I'm assuming based on waht you've said i should just give up.

 

[Thomas5000]

What appears to work is... If i format a sheet and unlock destinated cells and then cut and paste the worksheet on different tabs, the format is maintained. That way i don't have to format or unlock cells on each sheet.

The only problem is that i will have to redo all my formulas yet again and I'm concerned about having to redo my conditional formatting also.

 

[SkipVought]

I stand corrected.

You CAN select multiple sheets and FORMAT cells (in 3-D so to speak)

Skip,
[sub]
To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white] [/sub]

 

[SkipVought]

...which leads me to a question of your design: It would seem to follow that each of the grouped sheets has similar data, and storing similar data on different sheets is not a best and accepted practice.

Am I correct, that you have similar data on multiple sheets?

Skip,
[sub]
To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white] [/sub]

 

[Thomas5000]

Similar data on multiple sheets--Yes! Each sheet looks exactly the same. Much of it is tabular--a way of calculating Y and N.

I would rather create a db and have an application store the info, but that would take more time. So using Excel has been the easy solution for now.

I was ot able to select multiple sheets and unlock formatted cells. When you test the cells (in 3-D) as you put it, they don't format. What i've been able to do in Office2003 is Copy and paste that sheet on each of the following tabs and taht seems to work.

Any suggestions?

Thanks always Skip!

 

[SkipVought]

Hmmmmmm??? My 2003 formats - colors, borders, locked...

Skip,
[sub]
To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white] [/sub]

 

[Thomas5000]

Thanks Skip. You are right. I tried it again and it worked. But I know i had all my pages selected when I went through my worksheet and locked the cells i wanted protected.

Well, i guess i'll try it again. Thanks. Maybe I did something wrong.