excel - How to keep macro running when there are protected sheets? -

i have protected sheets 4 password because there cells users aren't allowed input in cells in sheet 4. password 1234.

but, want run macro, , if there error, cell highlight automatically.

my macro doesn't run , error, because cell want highlight in protected sheet.

how make sheet 4 stay protected , make macro keep running when click validation button?

private sub commandbutton1_click()  fileformat:=xlopenxmlworkbookmacroenabled, password:=1234, writerespassword:=1234, _     readonlyrecommended:=false, createbackup:=false  vehicle = sheets("4").range("k22")  expenditure_gasoline = sheets("4").range("m22")   if vehicle = true , expenditure_gasoline = 0         msgbox "it should not empty", vbcritical  end if  if vehicle = true , expenditure_gasoline = 0 sheets("4").range("m22").interior.colorindex = 3   end sub 

try changes bellow (untested)

---

v1 - protect sheet user changes, not vba changes userinterfaceonly:=true

---

option explicit  private sub commandbutton1_click()      const path_and_filename = "c:\yourtestfile.xlsx" '<------ update path & file name      dim wb workbook, ws worksheet, vehicle variant, expendituregasoline variant      set wb = workbooks.open(filename:=path_and_filename, writerespassword:="1234", _                             password:="1234", format:=xlopenxmlworkbookmacroenabled)     set ws = wb.sheets("4")      ws.protect password:="1234", userinterfaceonly:=true '<--- protect changes ui      set vehicle = ws.range("k22")     set expendituregasoline = ws.range("m22")      if not iserror(vehicle) , not iserror(expendituregasoline)         if vehicle = true , expendituregasoline = 0             ws.range("m22").interior.colorindex = 3             msgbox "cell m22 should not empty", vbexclamation         end if     end if end sub 

---

v2 - unprotect before change, , protect after change

---

private sub commandbutton1_click()      const path_and_filename = "c:\yourtestfile.xlsx" '<------ update path & file name      dim wb workbook, ws worksheet, vehicle variant, expendituregasoline variant      set wb = workbooks.open(filename:=path_and_filename, writerespassword:="1234", _                             password:="1234", format:=xlopenxmlworkbookmacroenabled)     set ws = wb.sheets("4")     set vehicle = ws.range("k22")     set expendituregasoline = ws.range("m22")      if not iserror(vehicle) , not iserror(expendituregasoline)         if vehicle = true , expendituregasoline = 0              ws.unprotect "1234"                     '<--- unprotect before change             ws.range("m22").interior.colorindex = 3             ws.protect "1234"                       '<--- protect back, after change              msgbox "cell m22 should not empty", vbexclamation         end if     end if end sub