Macro doesn't protect back my ws properly

Question

I am having a small issue with macro that is suppose to unprotect my worksheet, refresh Pivot Table and protect ws again (with condition). It works OK only partially as it does unprotect the ws, it refreshes the Pivot Table, but after that something strange is happening: - it doesn't really restore back rows and columns formatting - it doesn't protect the ws back properly (my ws looks like it is protected when you click on Tools,Protection - but, you can unprotect it without entering password again ??!!

    Sub RefreshPivotTables()
    ' will remove password and refresh PT
    Dim xpt As PivotTable
        With ActiveSheet
            .Unprotect Password:="milessss"
            For Each xpt In Worksheets("WT-1").PivotTables
                xpt.RefreshTable
            Next xpt
            .Protect Password:="milessss", AllowFormattingCells:=True, _
                AllowFormattingRows:=True, AllowFormattingColumns:=True, _
                AllowUsingPivotTables:=True, EnableOutlining:=True
        End With
    End Sub

Can anybody help please ? Cheers - Mile`S

Jean Rostan Jean Rostan · Accepted Answer · 2018-04-05T13:22:02

Simple typo mistake:

  .Protect AllowFormattingRows:=True
  .Protect AllowFormattingColumns:=True

Rows instead of Raws and Columns instead of Column

Source : Protection.AllowFormattingRows Property (Excel)

Typo was corrected, but to achieve your goal, you need to use this code:

Private Sub RefreshPivotTables()
' will remove password and refresh PT

Dim xpt As pivotTable
With ActiveSheet
  .Unprotect Password:="milessss"
  For Each xpt In .PivotTables
      xpt.RefreshTable
  Next xpt
  .Protect Password:="milessss", AllowFormattingCells:=True,AllowFormattingRows:=True, AllowFormattingColumns:=True, AllowUsingPivotTables:=True

End With
End Sub

In fact, you are using the Protect function multiple times with different options. So each time you use it, you erase the older options you used. Thus at the end of your macro, the only available property is AllowUsingPivotTables but no password it set. So you need to set all your arguments in a single expression.

Macro doesn't protect back my ws properly

1 Answers