Excel AddIn use in existing protected workbooks

Add-in Express™ Support Service
That's what is more important than anything else

Excel AddIn use in existing protected workbooks
Avoid password prompt in passsword protected sheets 
Subscribe
Michael Kaden




Posts: 208
Joined: 2017-11-15
Hallo,

I have an Excel AddIn which changes content in Excel Sheets by looping through all sheets.

When the user has a password protected sheet and the AddIn wants to change content, the password prompt comes up. It is clear, that the AddIn cannot change any content as the password is not known, but I want to avoid that the password prompt comes up, i.e. I want the looping just to go to the next sheet. As far as I know, there is no Worksheet.HasPassword property? I tried to do this by catching Error 1004 but the prompt still comes up. So what would be the best way (code) to do this?

Btw. I have protected sheets with no password and in this case I want to:

unprotect
change contents....
protect

so Worksheet.ProtectContents does not help


Thank you very much & kind regards

Michal
Posted 24 May, 2019 10:40:04 Top
Andrei Smolin


Add-in Express team


Posts: 16306
Joined: 2006-05-11
Hello Michael,

Before you modify a worksheet, you should check if all the modification types that your add-in requires are supported by the worksheet. I suggest that you check all Protect* properties available on the Worksheet object. Start with https://docs.microsoft.com/en-us/office/vba/api/excel.worksheet.protectcontents; look for Protect* properties in the left pane.

Regards from Belarus (GMT+3),

Andrei Smolin
Add-in Express Team Leader
Posted 27 May, 2019 05:55:52 Top
Henri Pellemans




Posts: 110
Joined: 2010-05-06
Private Sub unprotectThisSheetButton_OnClick(sender As System.Object,
                                                 control As AddinExpress.MSO.IRibbonControl,
                                                 pressed As System.Boolean) Handles unprotectThisSheetButton.OnClick
        Dim thisSheet As Excel.Worksheet = TryCast(ExcelApp.ActiveSheet, Excel.Worksheet)
        unprotectSheet(thisSheet)
        If Not IsNothing(thisSheet) Then Marshal.ReleaseComObject(thisSheet)
    End Sub


Public Sub unprotectSheet(thisSheet As Excel.Worksheet)

Dim myPassword As String = String.Empty
End Sub
Posted 27 May, 2019 06:27:52 Top
Henri Pellemans




Posts: 110
Joined: 2010-05-06

Private Sub unprotectSheet(thisSheet As Excel.Worksheet)

 Dim myPassword As String = String.Empty
 thisSheet.Unprotect(myPassword)
End Sub


    Private Sub protectSheet(thisSheet As Excel.Worksheet)

            thisSheet.Protect(DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:=String.Empty)

    End Sub
Posted 27 May, 2019 06:30:57 Top
Michael Kaden




Posts: 208
Joined: 2017-11-15
Dear Andrei, dear Henry,

thank you very much for your posts, but I think I was not clear enough in my question.

I want to avoid that the password prompt comes up if a worksheet is protected by password, but I want to unprotect with Vb.net code Worksheet.unprotect()the sheet, if there is no password.

Thank you & kind regards

Michael
Posted 27 May, 2019 10:11:39 Top
Andrei Smolin


Add-in Express team


Posts: 16306
Joined: 2006-05-11
Hello Michael,

The Excel object model provides no way to find out whether an empty string is used as a password on an Excel sheet.

Regards from Belarus (GMT+3),

Andrei Smolin
Add-in Express Team Leader
Posted 28 May, 2019 01:52:29 Top
Michael Kaden




Posts: 208
Joined: 2017-11-15
Dear Andrei,

sorry the issue was due to my ignorance.

I thought that if a sheet is protected with UserinterFaceOnly = False I could not manipulate the worksheet cells with the AddIn. I found out, that this is not the case, I can change the content of a protected sheet with the AddIn. So there is no need to unprotect the sheet. Also I thought that UserinterFaceOnly = False should be the default, but I am not sure it actually is.

So my topic is solved. Thank you all

kind regards & have a nice weekend.


Michael
Posted 02 Jun, 2019 03:21:41 Top
Andrei Smolin


Add-in Express team


Posts: 16306
Joined: 2006-05-11
Welcome!

Regards from Belarus (GMT+3),

Andrei Smolin
Add-in Express Team Leader
Posted 03 Jun, 2019 04:34:09 Top