Worksheet protection prevents Data Validation code from running...

[CHall]

I followed a tutorial to add a small bit of code to allow new items to be
added and automatically sorted in validation drop-down lists. Here's the site
with the tutorial:
http://www.contextures.com/excel-data-validation-add.html#Top

The code allows users to add new items to a list if it isn't already present
in the data validation drop-down list. It then automatically sorts the list.
Well it works great with an uprotected worksheet, but after I apply
protection, the code won't run. I even tried checking all items in the sheet
protection dialog box to see if one of them would allow the code to run but
it still blocks it.

Any ideas how to allow the code to run with sheet protection? Might I use
Allow Users to Edit Ranges under Sheet Protection?

 

[Paul Robinson]

Hi
Password protect the sheet with "mypassword" say.
Near the top of the worksheet_change code put
Activesheet.Unprotect password:="mypassword"

and at the end put
Activesheet.Protect password:="mypassword"

not tested, so may do something funny inside a change event, but you
could try it out.
regards
Paul