change validation on protected sheet

[Guest]

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)


Is there a way round this

Thanks

 

[Debra Dalgleish]

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.