Specifying User Permissions in VBA for Protected Excel Sheet -

i working spreadsheet required protected before distribution sheer sake of data integrity. have written function auto-populates column based on drop down list selection. not want user edit column have protected, in order auto-populate process un-protects , re-protects spreadsheet. issue lies.

i users have other permissions (e.g. formatting, row insertion, row deletion, etc). however, when process re-protects sheet, permissions revoked.

is there way can both lock sheet , specify user permissions grant in vba?

the worksheet.protect method allows specify available when performing review ► changes ► protect worksheet command. parameters largely optional need specified explicitly or blank parameters can passed in commas placeholders.

to protect worksheet password , allow column formatting , row insertion:

with worksheets("sheet one")     .protect password:="mypassword", contents:=true, _              allowformattingcolumns:=true, allowinsertingrows:=true     'insert row     .rows("9:9").entirerow.insert copyorigin:=xlformatfromleftorabove end 

see worksheet.protect method full list of available options.

another option userinterfaceonly. stops user predetermined actions on worksheet allows vba procedures perform actions otherwise restricted.

with worksheets("sheet one")     .protect password:="mypassword", userinterfaceonly:=true, contents:=true, _                  allowformattingcolumns:=true, allowinsertingrows:=true     'insert column; user cannot     .columns(2).entirecolumn.insert copyorigin:=xlformatfromleftorabove end 

this latter behavior allow more freedom in vba procedures without having continually unprotect , reprotect worksheet.