Hello all,

I am working on a workbook for a web page migration project. I have
developed a template with lots of validation; some through Data -
Validation lists and some use popups on the double-click event.

My problem is that when I finish the template, I am going to hand off
the template to another developer, who is programmatically going to
enter all the data and create lots of workbooks for various user
groups. Of course, this wipes out all the validation done by
Data-Validation, so I have code to add data validation back to the
appropriate columns every time the workbook is opened.

I also have a final validation routine that is called on the Save
event.

My problem is that when this workbook goes out to the users, mgmt wants
some of the cells to be read-only and they also want the users to be
able to copy and paste into other cells. But protecting the sheet
causes my code to recreate the data validation to break; you can't add
data validation to a protected sheet, even though the cell itself is
unlocked.

So, here's the problem: if the user copies and pastes into Column X,
the data validation is overwritten. When my validation routine runs,
if any of the pasted data is incorrect, the user will get an error
message and be told to select from the dropdown. But the dropdown will
no longer be there, having been overwritten by the pasting. (The
dropdowns contain up to 47 items, so I can't give the choices in the
error message.)

If you are still with me, my question is, can I use code to make cells
read-only in effect? Maybe using the selectionchange event to save the
old value to a global variable? i don't see a way to see that I have
LEFT a cell, though, to reset the cell value to the old value. Excel
seems to only have Target cells as parameters.

Has anyone ever done anything like this before??

Any ideas, war stories, references, resources, or good clean jokes
would be greatly appreciated.

Thanks,
Kathryn