Help with VAB unprotecting sheets

[Herpe_derp]

Hello

My code is set to wipe a workbook based on a date

Private Sub Workbook_Open()

Dim ws As Worksheet

' Check to see if specified date is passed
If Date >= DateSerial(2023, 7, 6) Then
' If it is, loop through all sheets
For Each ws In ActiveWorkbook.Worksheets
' Clear contents of each sheet
ws.Cells.ClearContents
Next ws
' Save workbook so data is permanently gone
ActiveWorkbook.Save
End If

End Sub

However, this is struggling to work on password protected sheets. Is there any way it can be modified to unprotect sheets before running?

The debug highlights ws.Cells.ClearContents yellow

 

[auto.pilot]

Try this...

It should work for sheets that are protected without a password. It will unprotect all sheets.

Sub RemoveProtection()
'
' This macro removes protection from all sheets
'

'
Dim WSheet As Worksheet
    For Each WSheet In Worksheets
    If WSheet.ProtectContents = True Then
    WSheet.Unprotect
    Else: GoTo x
End If
Next WSheet
    
    'ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
x:
End Sub

 

[Herpe_derp]

Thanks for this however, the sheets are password protected.

Could I also have help combining both macros into one?

Thanks

Try this...

It should work for sheets that are protected without a password. It will unprotect all sheets.

Sub RemoveProtection()
'
' This macro removes protection from all sheets
'

'
Dim WSheet As Worksheet
    For Each WSheet In Worksheets
    If WSheet.ProtectContents = True Then
    WSheet.Unprotect
    Else: GoTo x
End If
Next WSheet
   
    'ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
x:
End Sub

 

[Jaafar Tribak]

How many Worksheets are we talking about ?

 

[Herpe_derp]

How many Worksheets are we talking about ?

About ten sheets in one workbook, 3 of which are password protected all with the same password

 

[Jaafar Tribak]

About ten sheets in one workbook, 3 of which are password protected all with the same password

Try this :

Option Explicit

Private Sub Workbook_Open()

    Dim oWsh As Worksheet
    
    If Date >= DateSerial(2023, 7, 6) Then
        For Each oWsh In Me.Worksheets
            With oWsh
                If .ProtectContents Then
                    .Unprotect Password:="YourPassword"
                    .Cells.ClearContents
                    .Range("A1").ID = "Protected"
                End If
                If .Range("A1").ID = "Protected" Then
                    .Protect Password:="YourPassword"
                End If
            End With
        Next
        Me.Save
    End If

End Sub