VBA - Automatic Sheet Protection loses password

Question

We have an VBA Code which unprotects the sheet when opening and Protects it again, so we can use the group function, while protected.

Via a macro new info's are added in the Excel file. At the end of that code the sheet is protected again. It is always the same password with a global variable.

It happens if another user opens the file while it is used. The Excel file looses its password (unknown which it is) and have to be hacked via zip file trick.

Thank you for your help

Private Sub Workbook_Open()
    Passwort = "123"
    ActiveSheet.Unprotect (Passwort)
    Dim ws As Worksheet
    For Each ws In Worksheets
        ws.Protect userinterfaceonly:=True, Password:=Passwort, AllowFormattingCells:=True
        ws.EnableAutoFilter = True 'Autofilter
        ws.EnableOutlining = True 'Group
    Next ws

Editing part of the code:

ActiveSheet.Unprotect (Passwort)

LadenAusExternerDatei (Datum)

nachsteMaschine = 2

nextloop:

Err.Clear

aktuelleMaschine = Tabelle7.Range(Cells(nachsteMaschine - 1, 1), Cells(1500, 1)).End(xlDown).Row
nachsteMaschine = Tabelle7.Cells(aktuelleMaschine, 1).End(xlDown).Row
If aktuelleMaschine = Tabelle7.Cells(1048576, 1).End(xlUp).Row + zaehler Then
    For Each ws In Worksheets
     ws.Protect userinterfaceonly:=True, Password:=Passwort, AllowFormattingCells:=True
     ws.EnableAutoFilter = True 'ermöglicht Autofilter
    ws.EnableOutlining = True 'ermöglicht Gruppierung/Gliederung
    Next ws
    Exit Sub
End If
If nachsteMaschine >= aktuellMaschine + 1000 Then
     For Each ws In Worksheets
     ws.Protect userinterfaceonly:=True, Password:=Passwort, AllowFormattingCells:=True
     ws.EnableAutoFilter = True 'ermöglicht Autofilter
        ws.EnableOutlining = True 'ermöglicht Gruppierung/Gliederung
    Next ws
    Exit Sub
End If

It seems that the problem appears when you open the file and save changes... so could you show us the saving macro? — David García Bodego

TinMan TinMan · Accepted Answer · 2019-11-19T12:43:39

The UserInterfaceOnly setting is set to false when the workbook is first opened. You can re-apply UserInterfaceOnly without unprotecting the worksheet or workbook.

I recommend re-applying UserInterfaceOnly in the Workbook_Open event.

Private Sub Workbook_Open()
    ResetUserInterfaceOnly
End Sub

Sub ResetUserInterfaceOnly()

    Const Password = "123"

    Dim ws As Worksheet
    For Each ws In Worksheets
        ws.Protect UserInterfaceOnly:=True, Password:=Password, AllowFormattingCells:=True
        ws.EnableAutoFilter = True 'Autofilter
        ws.EnableOutlining = True 'Group
    Next ws

End Sub

VBA - Automatic Sheet Protection loses password

1 Answers