Worksheet protection prevents Data Validation code from running...
in [Programming]
|
From: CHall on 6 May 2010 09:29 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?
From: Paul Robinson on 6 May 2010 12:05 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 On May 6, 2:29 pm, CHall <CH...(a)discussions.microsoft.com> wrote: > 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?
|
Pages: 1 Prev: copy and paste data from different sheets Next: Display Math Symbols in UserForm |