Worksheet Protection

**MartinGTC** · 01-13-2012, 11:59 AM

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** · 01-13-2012, 12:14 PM

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)

Sub LockCells()
    Dim sh As Worksheet

    For Each sh In ActiveWorkbook.Sheets
      sh.Range("A1:B2").Cells.Locked = True
    Next sh

End Sub

**MartinGTC** · 01-13-2012, 12:22 PM

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** · 01-13-2012, 12:41 PM

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** · 01-13-2012, 12:47 PM

Try this:

Sub LockCells()
    Dim sh As Worksheet
    Dim UsdRngAddrss As String
    Dim sLocked As Variant
    Dim sHidden As Variant
    
    UsdRngAddrss = Workbooks("Master Workbook.xlsx").Sheets("Master Sheet").UsedRange.Address
    sLocked = Workbooks("Master Workbook.xlsx").Sheets("Master Sheet").UsedRange.Cells.Locked
    sHidden = Workbooks("Master Workbook.xlsx").Sheets("Master Sheet").UsedRange.Cells.FormulaHidden

    For Each sh In ActiveWorkbook.Sheets
        sh.Range(UsdRngAddrss).Locked = sLocked
        sh.Range(UsdRngAddrss).FormulaHidden = sHidden
    Next sh

End Sub

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"