excel vba - Using VBA to unprotect a sheet that has been protected with UserInterface Only = true -

i have issue worksheet protection @ moment. have read through forum , found userinterfaceonly = true useful avoid having unprotect sheet, enter code , re-protect.

however, have requirement unprotect sheets using macro (there people additional access amend worksheets don't want know main password), macro doesn't unprotect sheet.

i using following code in workbook protect , hide sheets on opening.

private sub workbook_open()     'unprotect workbook'     application.screenupdating = false      on error resume next     activeworkbook.unprotect password:="password"      'hide worksheets except project info , requisition. protect worksheets except template - allowing macros work while protected'     dim sheet worksheet     each sheet in worksheets         if sheet.name <> "project info" , sheet.name <> "requisition" , sheet.name <> "template" sheet.visible = xlsheethidden         if sheet.name <> "template" , sheet.name <> "task controls" sheet.protect password:="password", drawingobjects:=true, contents:=true, scenarios:=true _             , userinterfaceonly:=true, allowfiltering:=true         if sheet.name <> "template" sheet.enableselection = xlunlockedcells     next      activeworkbook.protect password:="password", structure:=true, windows:=false      application.screenupdating = true      end sub 

this works fine vast majority of workbook (and owe forum), when use following code unprotect sheet allow edited, sheet not unprotect. note used ok button of userform if makes difference

private sub ok_button_click()      dim supplier string     dim ws1 worksheet     dim ws2 worksheet     supplier = me.supplier_combobox.value     set ws1 = sheets("buyer's sheet")     set ws2 = sheets(supplier)      on error resume next     activeworkbook.unprotect password:="password"     ws2.visible = xlsheetvisible     activeworkbook.protect password:="password", structure:=true, windows:=false      ws2.select     set ws2 = activesheet     ws2.columns.hidden = false     ws2.unprotect password:="password"      msgbox "make required amendments price list , click button return home screen", vbokonly, "amend price list"      dim ctl control      each ctl in me.controls         if typename(ctl) = "textbox" or typename(ctl) = "combobox"             ctl.value = ""         elseif typename(ctl) = "checkbox"             ctl.value = false         end if     next ctl      unload me      end sub 

some of code inefficient (particularly use of .select) appears work , pretty stable. function not work worksheet.unprotect function.

looking more closely @ code after night's sleep, believe see issue. you're unprotecting workbook, re-protecting it, trying unprotect worksheet within protected workbook. not 100% that's issue, try this:

private sub ok_button_click()  'dim supplier string dim ws1 worksheet dim ws2 worksheet  set ws1 = sheets("buyer's sheet") set ws2 = sheets(me.supplier_combobox.value)  on error resume next activeworkbook.unprotect password:="password" ws2.visible = xlsheetvisible 'remove line leave workbook unptrotected while you're using 'activeworkbook.protect password:="password", structure:=true, windows:=false  'ws2.select 'set ws2 = activesheet 'note switched order of next 2 lines - unprotect first ws2.unprotect password:="password"   ws2.columns.hidden = false  msgbox "make required amendments price list , click button return home screen", vbokonly, "amend price list"  dim ctl control  each ctl in me.controls     if typename(ctl) = "textbox" or typename(ctl) = "combobox"         ctl.value = ""     elseif typename(ctl) = "checkbox"         ctl.value = false     end if next ctl  unload me  end sub 

now, based on text of msgbox, looks need leave sheet unprotected user make changes, (s)he clicks button re-protects , other processing. believe issue - worksheet protected, workbook protection overriding it. again, i'm not 100% on this, , workbook protection documentation , worksheet protection documentation aren't clear (to me) this.

my changes (cleaned couple of unnecessary lines near top), leaves workbook , worksheet unprotected user can make changes. i'm assuming button click referred in msgbox does, or modified to, re-protect everything.