Use of the protection object in VBA

Question

Is there any way to store the .protection object in VBA and use it to protect the sheet again with the parameters stored in the object?

I know it is possible to store the object but I can't use it to protect the sheet again.

I need this because different sheets have different protection profiles and I want to use this in a loop.

Set wsp = workbookname.Sheets(sheetname).Protection

and to protect the sheet again, I would like to use something like this:

workbookname.Sheets(sheetname).Protect wsp

Answer 1

You could e.g. create s class for each of your profiles with settings, example. HTH

' Class module SomeProtectionProfile

Private m_password As Variant
Private m_drawingObjects As Variant
Private m_contents As Variant
Private m_scenarios As Variant
Private m_userInterfaceOnly As Variant
Private m_allowFormattingCells As Variant
Private m_allowFormattingColumns As Variant
Private m_allowFormattingRows As Variant
Private m_allowInsertingColumns As Variant
Private m_allowInsertingRows As Variant
Private m_allowInsertingHyperlinks As Variant
Private m_allowDeletingColumns As Variant
Private m_allowDeletingRows As Variant
Private m_allowSorting As Variant
Private m_allowFiltering As Variant
Private m_allowUsingPivotTables As Variant

Private Sub Class_Initialize()
    m_password = "SomePsw1"
    ' and others like m_drawingObjects, m_contents ...
End Sub

Public Sub Protect(sheetToProtect As Worksheet)
    sheetToProtect.Protect Password:=m_password ' , ... and others
End Sub

' Standard module

Sub main()
    With New SomeProtectionProfile
        .Protect workbookname.Sheets(sheetname)
    End With
End Sub