Worksheet protection prevents Data Validation code from running...

C

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?
 
P

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top