7

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?

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
Pwi
  • 101
  • 1
  • 1
  • 6

2 Answers2

8

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.

Community
  • 1
  • 1
carlossierra
  • 4,479
  • 4
  • 19
  • 30
1

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

RGA
  • 2,577
  • 20
  • 38
  • The Excel file has 15 sheets and doing protection in every time takes 10 second which is too much. I' ve already experimenting with this. – Pwi Jul 13 '16 at 14:01
  • @Pwi 10 seconds for 15 sheets??? I can't see how it would take that long if all you are doing is toggling protection. I just tested it myself and it was essentially instantaneous. Is there something else going on that might be slowing it down? – RGA Jul 13 '16 at 14:03
  • I tried with a fully empty Workbook. Try with this code: `code: For Each Sh In Worksheets Sh.Protect Password:="passw", UserInterFaceOnly:=True Next sh /code` – Pwi Jul 13 '16 at 14:07
  • 1
    In Excel 2013 and up, the password algorithm was changed and it is a lot slower than it used to be. – Rory Jul 13 '16 at 14:18
  • Is there any way to protect sheet from user modifcation beside Protect/Unprotect method? – Pwi Jul 13 '16 at 14:20
  • @Rory for the first time in years, running 2010 is a good thing :) – RGA Jul 13 '16 at 14:21
  • @Rory I'm using 2013, this is why happen – Pwi Jul 13 '16 at 14:21
  • 1
    @RGA Running 2010 is always a good thing IMO. Still the best version. – Rory Jul 13 '16 at 14:22
  • @Rory Unfortunately i have no option running older version. – Pwi Jul 13 '16 at 14:22
  • @Pwi You're stuck with what you have then. – Rory Jul 13 '16 at 14:23