|
Michael Kaden
Guest
|
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: 18791
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.
Andrei Smolin
Add-in Express Team Leader |
|
Posted 27 May, 2019 05:55:52
|
|
Top
|
|
Henri Pellemans
Guest
|
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
Guest
|
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
Guest
|
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: 18791
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.
Andrei Smolin
Add-in Express Team Leader |
|
Posted 28 May, 2019 01:52:29
|
|
Top
|
|
Michael Kaden
Guest
|
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: 18791
Joined: 2006-05-11
|
Welcome!
Andrei Smolin
Add-in Express Team Leader |
|
Posted 03 Jun, 2019 04:34:09
|
|
Top
|
|