5

When I call Thisworkbook.Protect in VBA the protection of the workbook actually toggles from the current workbook's protection status instead of just protecting the workbook.

Thisworkbook.Unprotect doesn't do this though.

Anyone know why this is the case or how to get around it?

aynber
  • 22,380
  • 8
  • 50
  • 63
KingKong
  • 411
  • 3
  • 10
  • 26
  • 3
    I've never noticed that. It's true in Excel 2016 on my computer. You could test using `If Thisworkbook.ProtectStructure` or `If Thisworkbook.ProtectWindows`. Also, have you looked at `UserInterfaceOnly`, which can eliminate the necessity to Protect and Unprotect in code? – Doug Glancy Feb 13 '16 at 02:30
  • 1
    UserInterfaceOnly doesn't work for me as I have some code that modifies things that still requires unprotection. I did a little research and this seems to be a known issue. Checking the Protection status does work though. – KingKong Feb 17 '16 at 02:30

1 Answers1

4

Not sure if it is by design or a bug... If you provide the parameters, it works fine:

ActiveWorkbook.Protect ,true,true

or

ActiveWorkbook.Protect Structure:= true

Whichever you need/want. This way it won't turn the protection off, only on.

To turn the protection off, you can use this (assumning you only turned on structure protection):

ActiveWorkbook.Protect Structure:= false

Parameters are:

.Protect(Password, Structure, Windows)
vacip
  • 5,246
  • 2
  • 26
  • 54