Look in Excel's help for: Features that are unavailable in shared workbooks

You'll see this:

Unavailable featu Add or change data validation

Alternatives: Cells continue to be validated when you type new values,
but you can't change existing data validation settings.


arewa wrote:

I have a shared workbook that runs a macro with a validation. The macro
references the cells below it, which contain the validated macro and creates
blank copies of the cells. When the workbook is unshared, the macro runs fine
and creates the new rows with the validation. When the workbook is shared,
however, the macro only creates the new rows, it does not put in the
validation.

--

Dave Peterson