Macro Clear Button - Reset Protect with Format Rows Option

[JenMcG]

Hello,

I've created a macro to clear the unprotested cells in a worksheet. In order for this to work I've built into the macro to unprotect the sheet (at the beginning) and then re-protect it (at the end). This serves my purpose except it does not reset the options to format rows. How can I add this?

Sub Clearcells1()
ActiveSheet.Unprotect Password:="1300church"
Range("C2:D2", "C3:D3").ClearContents
Range("B8", "F27").ClearContents
Range("B32", "F34").ClearContents
Range("F39", "F42").ClearContents
Range("E42").ClearContents
Range("E46").ClearContents
Range("E51").ClearContents
Range("F52", "F53").ClearContents
Range("E54").ClearContents
Range("E58").ClearContents
Range("F64").ClearContents
Range("F68").ClearContents
ActiveSheet.Protect Password:="1300church"
End Sub

 

[Jeffrey Mahoney]

I recorded this macro with all the protection methods I wanted on my sheet. You can do the same

ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
        , AllowFormattingCells:=True, AllowFormattingColumns:=True

 

[JenMcG]

I recorded this macro with all the protection methods I wanted on my sheet. You can do the same

ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
        , AllowFormattingCells:=True, AllowFormattingColumns:=True

Thank you- where should this be inserted in the macro?

 

[Jeffrey Mahoney]

It should replace the line with Activesheet.protect. You, of course will have to add your password at end. I encourage you to record a macro where you set the parameters you need for the protected sheet.

ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
        , AllowFormattingCells:=True, AllowFormattingColumns:=True, Password:="1300church"

 

[JenMcG]

Thank you very much. It worked!