vba excel changing the BuiltinDocumentProperties on a workbook encrypted with a password-CodePudding

I have a workbook that requires a password to open it. When the workbook is opened (with password) and data in a cell is changed, I want to change the BuiltinDocumentProperties according this cell value, before the document is saved. Therefore I'm trying to use the Workbook_BeforeSave event, but I'm only able to change the BuiltinDocumentProperties with the password protection disabled (workbook.unprotect). Is it possible to set the password (for opening the workbook), without using the workbook.saveas password:="xxx" method, because this loops thru the Workbook_BeforeSave again.

CodePudding user response：

I've tested this in Excel 2010 and had no issues changing BuiltinDocumentProperties in a protected book nor with one that simply requires a password to open. So either it's a specific Property (you didn't say which) or it's a later office thing.

Don't have a later office where I am today (so can't test that). Either way, I'll assume you know the password. If so, why don't you simply:

Option Explicit

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    
''' Example sets Keywords to content of A1 (in the active sheet)
    With ThisWorkbook
        .Unprotect "<Password>"
        .BuiltinDocumentProperties("Keywords") = Range("A1")
        .Protect "<Password>", Structure:=True, Windows:=False
    End With

End Sub

CodePudding user response：

Thanks for your reply. I'm using this with Office 365. I try to change the Comments property to the value of a cell before saving. When the file is password encrypted I'm not able to do this.

I now have the following solution: When the value of the Comments cell in the workbook is changed. I will reset the password with:

Sub ResetPasswordOptions()
    Application.DisplayAlerts = False
    ThisWorkbook.SaveAs Filename:=ThisWorkbook.FullName, FileFormat:=xlOpenXMLWorkbookMacroEnabled, Password:=""
    Application.DisplayAlerts = True
End Sub

Then I will change the BuiltinDocumentProperties

And after that I set the password protection:

Sub SetPasswordOptions()
    Application.DisplayAlerts = False
    ThisWorkbook.SaveAs Filename:=ThisWorkbook.FullName, FileFormat:=xlOpenXMLWorkbookMacroEnabled, Password:="<password>"
    Application.DisplayAlerts = True
End Sub