Macro to protect sheet but allow row insertion

[excelinter]

Hi,

I have a excel workbook with several worksheets and I want to be able to protect all the formulas in the workbook, but allow row/column insertion, deletion, formatting of cells and expanding/collapsing groups of rows. I have several workbooks on which I need to perform this action. So, I got the macro below for performing this action - the macro protects all the cells which have formulas, but then it does not allow me to format rows/columns/cells or insert rows/columns even though I have explicity set those parameters to True. I am using Excel 2007. Can some take a look and let me know what I'm missing. Any help is greatly appreciated !


Sub protect_formulas()
Dim wks As Worksheet
Dim wkbk As Workbook
Dim cl As Range
Set wkbk = ActiveWorkbook
For Each wks In wkbk.Worksheets
wks.Unprotect Password:="budget"
wks.Cells.Locked = False
For Each cl In wks.UsedRange
If cl.HasFormula Then
cl.Locked = True
End If
Next cl
wks.Protect Password:="budget"
wks.Protect AllowFormattingColumns:=True
wks.Protect AllowInsertingColumns:=True
wks.Protect AllowInsertingRows:=True
wks.Protect AllowFormattingRows:=True
wks.Protect AllowFormattingCells:=True

Next wks

End Sub

 

[Peter_SSs]

Instead of protecting the sheet once with 6 conditions, you are protecting the sheet 6 separate times each with 1 condition. This means that only the last condition (AllowFormattingCells) will remain applied. You can check for example by running your code and then manually unprotecting the sheet. You will find that you can do so without a password.

I think you should also be able to lock all the formula cells at once, rather than looping through each cell in the UsedRange.

Test in a copy of your workbook.

Sub protect_formulas2()
    Dim wks As Worksheet
    Dim wkbk As Workbook
    
    Set wkbk = ActiveWorkbook
    For Each wks In wkbk.Worksheets
        wks.Unprotect Password:="budget"
        wks.Cells.Locked = False
        
        On Error Resume Next
        wks.UsedRange.SpecialCells(xlCellTypeFormulas).Locked = True
        On Error GoTo 0
        
        wks.Protect Password:="budget", AllowFormattingCells:=True, _
            AllowFormattingColumns:=True, AllowFormattingRows:=True, _
            AllowInsertingColumns:=True, AllowInsertingRows:=True
    Next wks
End Sub

Also note that you will get more potential helpers if you take note of the last point in my signature block below.

 

[excelinter]

Peter,

Thanks so much for the quick reply. I was able to use your code successfully ( I did have to declare and set wkbk, because I was using it to cycle through all the sheets in the workbook).

However, I now have a different problem. Once I insert a new row in the protected worksheet, I am unable to write a formula in the newly inserted row in certain cells i.e for example, in row 9, I have a formula on cell S9 to sum everything between G9 to R9, but when I insert a new row below row 9, i.e. say the new row 10, I am unable to write a formula in S10 to sum everything from G10 to R10. While I'm unable to put in a new formula just below a cell which has a formula, I'm able to edit other cells in the new row. Is there any way to work around this through VB code ?

Thanks again.

P.S: Sorry about my not putting my code in the proper format in the previous post - was not aware of that provision. Also I'm very new VBA, so pardon the questions if they seem very basic.

 

[Peter_SSs]

I did have to declare and set wkbk, because I was using it to cycle through all the sheets in the workbook

Yes, I subsequently realised that and altered my post accordingly.

However, I now have a different problem. Once I insert a new row in the protected worksheet, I am unable to write a formula in the newly inserted row in certain cells i.e for example, in row 9, I have a formula on cell S9 to sum everything between G9 to R9, but when I insert a new row below row 9, i.e. say the new row 10, I am unable to write a formula in S10 to sum everything from G10 to R10. While I'm unable to put in a new formula just below a cell which has a formula, I'm able to edit other cells in the new row. Is there any way to work around this through VB code ?

No easy way that I can think of. In any case, if you could do that then the new formula cell would not be protected and you would have to run the previous code again to protect it.

So I think you would have to unprotect the sheet to insert the formula. This may be possible to automate but I think would be tricky because inserting a row does not trigger any vba 'Events' that could be used to flag that a new formula may be required.

If it is just formulas in column S then perhaps you could have another manually triggered macro that unprotects the sheet and copies the column S formulas down?

 

[excelinter]

Unfortunately its not just Column S, but I need to have that capability throughout the workbook. The thing is I have created a template that I will be distributing to several people and I only want to protect formulas, but allow them to do all other actions including inserting/formatting rows/columns/cells. Given this, I'm thinking of avoiding protecting the sheet altogether and clearly color code cells so that formula cells are not overwritten.

Thanks again for the input and suggestions.