View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.programming
Debra Dalgleish Debra Dalgleish is offline
external usenet poster
 
Posts: 2,979
Default change validation on protected sheet

If the workbook is shared, you can't change the protection.
If the sheet is protected, you can't change the data validation on any
cell (locked or unlocked).

If you need to change the validation, you could wait till no one is
using the workbook.
If you're tracking changes, you can list all the changes on a new sheet.
Copy the data from the History sheet to a new sheet (e.g. HistoryPrev)
Then, turn off sharing for the workbook, and unprotect the sheet
Change the validation, reprotect the sheet, and turn sharing on.

Patrick wrote:
I have a shared and protected sheet
Although the cells in question are unlocked VBA will not allow the
validation to be changed.

(Works fine if the sheet is unprotected but protection is necessary)


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html