Protect Sheet Allowing Users to Format Rows/Edit Objects?

**Kristine T.** · 11-16-2009, 01:18 PM

I want my macro to protect my sheet again after it is done to allow editing objects and formatting rows. I am really stuck and have come up with the below, but it isn't working. Please help!

Sheets("TA Form").Protect Password:="1234", AllowEditObjects:=True, FormattingRows:=True

**Kristine T.** · 11-16-2009, 02:26 PM

Okay, I made this work. However, when I put in AllowEditingObjects or AllowEdit Objects it doesn't work. Does anyone know how I should write that last bit so it works?

ActiveSheet.Protect Password:="1234", AllowFormattingRows:=True, AllowSorting:=True

**Kristine T.** · 11-16-2009, 02:56 PM

Okay, I found the below, but I don't know how to encorporate it in my WorkbookAfterPrint (). I have both posted below. My sheet is named "TA Form", so I assume the With Sheet1 should be changed to With Sheets ("TA Form")?

Private Sub Workbook_Open() 
    With Sheet1 
        .Protect  Password:="", UserInterfaceOnly:=True 
        .EnableOutlining = True 
        .Protect DrawingObjects:=False, Contents:=True, Scenarios:= _ 
        True 
    End With

Private Sub WorkbookAfterPrint()
    ActiveSheet.Unprotect Password:="1234"
    ThisWorkbook.Worksheets("TA Form").Range("A89").Interior.ColorIndex = 8
    ThisWorkbook.Worksheets("TA Form").Range("A191:A192").Interior.ColorIndex = 8
    ThisWorkbook.Worksheets("TA Form").Range("H89").Interior.ColorIndex = 8
    ThisWorkbook.Worksheets("TA Form").Range("D155").Interior.ColorIndex = 8
    ThisWorkbook.Worksheets("TA Form").Range("U155").Interior.ColorIndex = 8
    ThisWorkbook.Worksheets("TA Form").Range("AA193").Interior.ColorIndex = 8
    ThisWorkbook.Worksheets("TA Form").Range("K155:P155").Interior.ColorIndex = 8
    ThisWorkbook.Worksheets("TA Form").Range("AD155:AH155").Interior.ColorIndex = 8
    ThisWorkbook.Worksheets("TA Form").Range("A162:U162").Interior.ColorIndex = 8
    ThisWorkbook.Worksheets("TA Form").Range("AB162:AH162").Interior.ColorIndex = 8
    ThisWorkbook.Worksheets("TA Form").Range("I199").Interior.ColorIndex = 8
    ActiveSheet.Protect Password:="1234", AllowFormattingRows:=True
End Sub

**Kristine T.** · 11-16-2009, 03:16 PM

I solved it. Here is the answer....

Private Sub WorkbookAfterPrint()
    ActiveSheet.Unprotect Password:="1234"
    ThisWorkbook.Worksheets("TA Form").Range("A89").Interior.ColorIndex = 8
    ThisWorkbook.Worksheets("TA Form").Range("A191:A192").Interior.ColorIndex = 8
    ThisWorkbook.Worksheets("TA Form").Range("H89").Interior.ColorIndex = 8
    ThisWorkbook.Worksheets("TA Form").Range("D155").Interior.ColorIndex = 8
    ThisWorkbook.Worksheets("TA Form").Range("U155").Interior.ColorIndex = 8
    ThisWorkbook.Worksheets("TA Form").Range("AA193").Interior.ColorIndex = 8
    ThisWorkbook.Worksheets("TA Form").Range("K155:P155").Interior.ColorIndex = 8
    ThisWorkbook.Worksheets("TA Form").Range("AD155:AH155").Interior.ColorIndex = 8
    ThisWorkbook.Worksheets("TA Form").Range("A162:U162").Interior.ColorIndex = 8
    ThisWorkbook.Worksheets("TA Form").Range("AB162:AH162").Interior.ColorIndex = 8
    ThisWorkbook.Worksheets("TA Form").Range("I199").Interior.ColorIndex = 8
    ActiveSheet.Protect Password:="1234", DrawingObjects:=False, AllowFormattingRows:=True
    End Sub

**DonkeyOte** · 11-16-2009, 03:54 PM

Thank you for posting your solution.

If interested you can format all ranges simultaneously:

ThisWorkbook.Worksheets("TA Form").Range("A89,A191:A192,H89,D155,U155,AA193,K155:P155,AD155:AH155,A162:U162,AB162:AH162,I199").Interior.ColorIndex = 8