CODEWITHSUNDEEP
vbaexcelexcel-2010password-protection
Pwi
Pwi

Reputation: 111

Sheet protection: UserInterFaceOnly gone

In VBA Excel if I protect sheets with UserInterFaceOnly:=True option after I close and open the file again the UserInterFaceOnly mode is not active, only Password protection.

The code: ActiveSheet.Protect Password:="myPassword", UserInterfaceOnly:=True

Why?

Upvotes: 8

Views: 17585

Answers (2)

carlossierra
carlossierra

Reputation: 4707

You can't do it without reapplying UserInterfaceOnly:=True after reopening the workbook. Taken from Excel's Vb protect method reference:

If you apply this method with the UserInterfaceOnly argument set to true and then save the workbook, the entire worksheet (not just the interface) will be fully protected when you reopen the workbook. To re-enable the user interface protection after the workbook is opened, you must again apply this method with UserInterfaceOnly set to true

Now, if your concern is that this takes too long (15 seconds, as you say), take a look at this Code Review answer. I have done this in several workbooks of varying level of complexity, and the time for reapplying protection is negligible in all versions I've tried, including 2010.

Upvotes: 9

RGA
RGA

Reputation: 2607

I'm not sure what the cause of that issue is, but you can circumvent it by adding protection code to the Workbook_Open() event, resetting every sheet protection to have UserInterfaceOnly:=True in each

Upvotes: 1

Related Questions