excel - Cannot set Locked-property despite UserInterFaceOnly=True and no merged cells -

i setting locked property of small range, code fails familiar 1004 cannot set locked property of range class, similar this problem.

• the range in question not contain merged cells
• my sheet locked userinterfaceonly=true

i out of ideas - how can find out causing fail?

edit: failed mention locking sheet, saving it, closing , re-opening - , code setting locked-property triggered in workbook_open.

this leads design issue caused fact re-opening workbook removes interface-only protection, leaving entire sheet protected. @clr putting me on path, , credit goes him if decides submit answer.

lock code:

sub locksheet()     if ws1.protectcontents = false ws1.protect password:="1", userinterfaceonly:=true end sub 

code snippet fails when sheet protected (but works if unprotect sheet):

with summaryrange     .locked = not (someboolvar) ' <-- 1004 cannot set locked etc.     .formulahidden = not (someboolvar) end 

where summaryrange qualified , working in other parts of code:

set summaryrange = ws1.range(firstsummarycolumn & "4:" & lastsummarycolumn & lastrow) 

& verified working compiler:

? module1.summaryrange.address $j$4:$m$50 

i have tried reproduce problem , can't, using below subs mimic code.

sheet protection:

sub locksheet()     dim ws1 worksheet     set ws1 = thisworkbook.sheets("sheet1")      if ws1.protectcontents = false         ws1.protect password:="1", userinterfaceonly:=true     else         ws1.unprotect password:="1"     end if end sub 

range locking:

sub lockit()     dim ws1 worksheet     set ws1 = thisworkbook.sheets("sheet1")     dim someboolvar boolean     someboolvar = true     dim summaryrange range     set summaryrange = ws1.range("$j$4:$m$50")      summaryrange         .locked = not someboolvar ' <-- no error triggered here         .formulahidden = not someboolvar     end         end sub 

possible causes:

• your summaryrange not defined: have debunked in q.
• something fishy happening sheet locking: have debunked above.
• your someboolvar not defined. note in above code, have defined someboolvar true, , code works. try debugging ?someboolvar in immediate window see before used. edit: have debunked too.

---

edit:

as suggested in comments, issue userinterfaceonly flag gets reset false when workbook closed. sidestep this, have re-apply protection when workbook opens. sub achieve that:

sub reprotect() ' called workbook_open event     ' cycle through sheets     dim sh worksheet     each sh in thisworkbook.sheets         ' if protected, reprotect reset userinterfaceonly flag         if sh.protectcontents = true             sh.unprotect password:="1"             sh.protect password:="1", userinterfaceonly:=true         end if     next sh end sub 

fortunately, locked property of ranges not affected workbook being closed, not have re-apply condition again!

the office vba documentation doesn't address problem, vb documentation (often comparable) in fact does:

if apply method userinterfaceonly argument set true , save workbook, the entire worksheet (not interface) protected when reopen workbook. re-enable user interface protection after workbook opened, must again apply method userinterfaceonly set true.