Can't add Data Validation to Protected Sheet

I'm using a macro to add Data Validation (xlValidateList) to a sheet. I can only do it if the sheet is unprotected. I have the cells unlocked, and I'm using .Protect userinterfaceonly:=True to protect the sheet.

Am I missing something that would let me add the Validation to a protected sheet?

Without thinking too deeply, why not unprotect, add validation, and re-protect?

My dim recollection is that UIO is not really as encompassing as it might be.

Just reiterating shg's point - I don't believe you can modify the DV without doing as he says (ie UIO is not sufficient in this instance).

It's unreliable. Sometimes works, sometimes not. Much better to do as shg said.

romperstomper;

I'm curious. This is the first time I've run across anything that didn't make sense to me when using userinterfaceonly.
Do you know of other situations that don't work?

Data Validation is the only one I know of offhand that fails regularly but, as I tend to unprotect and reprotect anyway on the rare occasions I even bother with protection and have to alter something, I don't classify myself as an expert.

i'm experiencing the same thing. strangely, as another pointed out, it does work sometimes.

when i hit the error, i click "Debug", and then immediately continue running the code. then it works.

the reason i would like to avoid the unprotect/protect cycle, is that i need to do this every single time the user clicks a cell on the sheet. so, in the interest of a quality user-experience, the code needs to be as quick as possible.

the reason i need to remove the validation temporarily, and then immediately re-add it to the cells, is because ActiveWindow.RangeFromPoint is intercepted by in-cell dropdown lists.

if i could find a way to get ActiveWindow.RangeFromPoint to work without disabling Validation.InCellDropdown, then i could avoid this whole issue! --and improve the user-experience.