'Protect worksheet not allowing all exceptions

I have a macro that unprotects a worksheet with a password, formats some things, then re-protects it with a password and some exceptions.

However, when reprotecting the workbook, only one exception is kept and the password is not recorded.

After the macro runs, It doesnt require the password to unprotect and I can only insert rows despite wanting the ability to format cells, rows, and columns too.

Below is the code:

Sub format()

Set summary = ThisWorkbook.Worksheets("SUMMARY")

summary.Unprotect Password:="5TILNA102"

' Do_stuff...

With summary
    .Protect Password:="5TILNA102" ' this doesnt work
    .Protect AllowFormattingCells:=True ' this doesnt work
    .Protect AllowFormattingColumns:=True ' this doesnt work
    .Protect AllowFormattingRows:=True ' this doesnt work
    .Protect AllowInsertingRows:=True ' this works
End With
      
Application.ScreenUpdating = True
                            
End Sub

excel vba

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution	Source

'Protect worksheet not allowing all exceptions

Sources

Related Questions