How to Protect and Unprotect the same data range in multiple sheets AND modify this range each month. Excel 2010

[varilian]

This forum has already helped me to add a macro to protect and unprotect all the sheets in my work book. The one piece of the puzzle I am now missing is the ability to quickly change the range which is left open to be edited in all the sheets at once. Currently I can now protect and unprotect all the sheets with one button, however I have to go into each tab individually to edit the range that is left unprotected.

Just in case the above is not clear I can explain why I need this ability. The document is used for forecasting; each tab is for a different manager and every month the actual figures are populated. I need to lock these actuals so they can't be edited and only allow future forecast months to be updated. Resulting in a need to change the range that can be edited each month on every single tab.

I hope some one can help.

 

[Andrew Poulsom]

Welcome to MrExcel.

To unlock A1:A10 on the unprotected worksheet Sheet1:

Worksheets("Sheet1").Range("A1:A10").Locked = False

 

[varilian]

Andrew,

Thanks for the quick reply. I'm very new to VBM, could you give me a bit more detail on how I should use this code. I currently use the following to lock and unlock all the sheets (I have included a blank 'start' and 'end' tab with my sheets in-between).

Sub ProtectAll()


Dim wb As Workbook
Dim ws As Worksheet


Set wb = ActiveWorkbook


For Each ws In wb.Worksheets
ws.Protect DrawingObjects:=True, _
Contents:=True, Scenarios:=True, _
Password:="biscuits"
Next ws


Set wb = Nothing
Set ws = Nothing


End Sub


Sub DeProtectAll()


Dim ws As Worksheet


For Each ws In ActiveWorkbook.Worksheets
ws.Unprotect Password:="biscuits"
Next ws


End Sub


Do I use your code after I have locked or unlocked them to then change the area that they will be able to edit?
Thanks again for your help

 

[Andrew Poulsom]

You would put the code to lock/unlock ranges in the DeProtectAll procedure, inside the For Each ... Next loop.

 

[varilian]

Andrew, I fear my lack of knowledge is letting me down, I have attempted to add the line as you suggest but it no longer works. Could you show me what the code should look like under the following circumstances: Three sheets titled 'A Ball', 'J Lyon', 'T Guy'. All requiring the range R5:X1000 to be the only part free to be edited when the sheet is protected.

Thanks

 

[Andrew Poulsom]

What did you try that didn't work? Maybe:

Sub DeProtectAll()
    Dim ws As Worksheet
    For Each ws In ActiveWorkbook.Worksheets
        With ws
            .Unprotect Password:="biscuits"
            If .Name = "A Ball" Or .Name = "J Lyon" Or .Name = "T Guy" Then
                .Cells.Locked = True
                .Range("R5:X1000").Locked = False
            End If
        End With
    Next ws
End Sub

 

[varilian]

I've added this and there is no change in how it operates. I bet you wish you hadn't answered this one now Andrew, but I'm determined to solve this. My aim is:
Run one Macro and have all sheets completely unlock -DONE
Run one Macro and have all sheets locked apart from an identical range in each one - Also DONE.

The final piece I can't get is for me to be able to edit the range in an easier way than sheet by sheet in the following manner:
- Review tab - Allow Users to Edit Ranges - Set the range I want them to edit - click protect sheet, enter password.
- Repeat the above for every individual tab. Every month reducing the range that can be edited by one column, individually in each sheet.

 

[Andrew Poulsom]

Sorry, it's not possible other than sheet by sheet.

 

[varilian]

Sad to hear, thanks for trying