Protecting a workbook on close but allowing certain formats

[Andy & Kate]

I have written a macro to unfilter a workbook when it is saved but it also needs to unprotect and then protect up the workbook. My problem is that the protection needs to allow certain formatting. I have tried the following but it doesn't allow the formating:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

Sheets("OUTPUTS").Select
ActiveSheet.Unprotect ("investment")
If ActiveSheet.FilterMode Then ActiveSheet.ShowAllData
ActiveSheet.Protect ("investment")
AllowFormattingCells = True
AllowFormattingColumns = True
AllowFormattingRows = True
AllowFiltering = True
AllowEditRanges = True

End Sub

Any suggestions would be greatly appreciated.

 

[Von Pookie]

All of those formats you want to allow are different parameters of the Protect statement and they have to be on the same line--they can't be separated like you currently have them. Plus, the "AllowFormatCells" etc. are labels. You have to use a ":=" between the label and the value if you want to use them.

Untested, but try:

With Sheets("OUTPUTS")
    .Unprotect ("investment")
    If .FilterMode Then .ShowAllData
        .Protect Password:="investment", AllowFormattingCells:=True, _
        AllowFormattingColumns:=True, _
        AllowFormattingRows:=True, AllowFiltering:=True, _
        AllowEditRanges:=True
    End If
End With