Menu
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?
465
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
No user input is allowed any more. One option is to unprotect the worksheet, run the code / macro, and then protect it again, as shown below: Sheet1.Unprotect Password:="abc" 'Enter Code / Macro Sheet1.Protect Password:="abc" If the code encounters an error or gets interrupted, your worksheet will remain unprotected;
UserInterfaceOnly Optional Variant Trueto protect the user interface, but not macros. If this argument is omitted, protection applies both to macros and to the user interface. AllowFormattingCells Optional Variant Trueallows the user to format any cell on a protected worksheet. The default value is False. AllowFormattingColumns Optional Variant
Setting the UserInterfaceOnly argument to True means that the worksheet protection applies only to the user interface and does not apply to macros and this will allow Excel to run all macros in the worksheet. If this argument is omitted, protection applies both to macros and to the user interface.
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.
155
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
43
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
