Any way to enable data validation on protected sheet?

[ExcelNoob222]

I have a protected sheet that has drop downs through data validation in a cell. They work great. However, the use may need to copy those cells to more rows. With the sheet protected, the drop down options do not copy to the new rows (if the sheet is unprotected, they do). Unfortunately the workbook is going external so I cannot have it as a macro enabled workbook otherwise I could just do some sort of button that unprotects, copy and paste the cells, then protects the worksheet again.

Is there any way to allow data validation when the sheet is protected through VBA? (Hoping the VBA can be ran against the sheet instead of living in the workbook). I have a feeling the answer on this is going to be no....but threads on this seem to be from a few years ago so I am hoping theres been an update to excel in the meantime!!

Thanks for the help!

 

[Fluff]

Duplicate to: Data Validation lost when Copy & Pasting

In future, please do not post the same question multiple times. Per Forum Rules (#12), posts of a duplicate nature will be locked or deleted.

In relation to your question here, I have closed this thread so please continue in the linked thread.