Worksheet Protection

[MartinGTC]

Hi guys, I have a quick query....

I have a worksheet with many complex formluas in order to achieve a pricing sheet. Some of these cells are unlocked - others are locked (but selectable).

My question is this - i have approx 92 different sheets over 14 workbooks that need this protection - do I need to do the boring and mundane task of, hold ctrl, click, drag, release, repeat etc etc, lock - and then repeat, or can I copy the same cell protection over many sheets?

Thanks!
M

[Richard Buttrey]

Hi,

Not necessarily

If the range of cells is the same on each sheet, or if they're different but there's some rule which can be applied to find them, then a simple macro to loop through all sheets and set the range would do the trick.

e.g. - assuming all cells are the same. (Will need modification if not)

Please Login or Register  to view this content.

[MartinGTC]

Hey,

I've never used Macros in a spreadsheet before - any help with this? Also the cells are the same on every sheet, but the unlocked cells are all over the place with individual cell references (rather than a more simple A1:B2, which I assume can be selected with eg A1, A2, B1, B2 etc)

Thanks

[Richard Buttrey]

Hi,
I probably misled you, and others, since by definition when you switch on sheet protection all cells are locked by default unless you have specifically unlocked them. My example macro should have said ..... Locked = False.

I should have asked the obvious. Are you wanting to unlock all cells that are NOT formulae? If so just use the F5 (Goto) key, pick Special and then Constants. This will select all those cells. Then just format them as Unlocked.

Is there anything about the cells that you want to unlock that can be identified or worked out. e.g. are they or their text always the same unique colour, are they always x rows and y columns away from some other identifiable cells, or some other rule.
For instance what's the manual process you would go through to achieve this and is that easily written down in a logical way.

[Whizbang]

Try this:

Please Login or Register  to view this content.

From your active workbook, hit the keys ALT+F11. Insert a module by Right-clicking on the workbook name in the navigation pane on the left. Select "Insert" -> "Module". Paste the above code into this module. Then put your cursor in the pasted code and go to "Run" -> "Run Sub/Userform"

[MartinGTC]

Hi, thanks for the replies.

Some cells are, and some aren't formulas unfortunately!

I feel like I have to do a big repeat of locking soon lol