2
I have an Excel spreadsheet which serves as a data dump/transfer facility from an in-house application.
The spreadsheet also acts as a crude data-entry facility, as a modified spreadsheet can be imported back into the app. As such the spreadsheet is occasionally given to external users.
We have a couple of columns (say 3 and 7) where the cells have data validation applied. The thing is, the validation lists are not necessarily the same for every cell in the column. For example, the cell (R1,C3) might have allowable values of A,B,C; for the cell (R2,C3) it might be D,E,F; and so on.
The issue arises if a user copies cell (R1,C3) to (R2,C3) -- either via a normal copy/paste, or using Fill Down. It appears that the validation gets copied along with the cell values and any formatting. But now cell (R2,C3) contains an invalid value -- but since the validation rule was copied/changed along with the value, the "error" is not caught. (I'm guessing this behavior is intentional.)
Is there a way to "lock" the validation on a cell so it doesn't get overwritten on a copy?
The users expect to be able to edit the cell data, so protecting the sheet is not an option. I'd prefer not to have to resort to VBA if possible -- the application from which these spreadsheets are generated is due to be retired shortly, so we really don't want to add to the maintenance burden.
I understand this may not be feasible. (But I have to ask.) I also understand this may be an X Y problem; if there's a better way to attack the issue, I'm all ears.
David
Posted 2017-02-23T18:10:32.613
Reputation: 391
1You can copy and
paste special valuesin that case the validation rules will stay – yass – 2017-02-23T19:26:43.9831the copy + paste special is the preferred option here, but you could also use a formula for data validation that covers all possibilites, so it is the same formula for all cells. Much harder option, specially if you have a lot of rules for the allowable values – Thales – 2017-02-25T21:34:52.823
Copy and Paste Special/Values is not really an option. Not all our users are that sophisticated, and we occasionally give the spreadsheet to external users (see edit). The formula idea had occurred to me, and that wouldn't be too hard -- it would just be based off a single discriminant value. Unfortunately that discriminant is not currently included on the spreadsheet; I'd have to add a column, and that would be a "breaking" change and therefore not worth it. Thanks anyway. – David – 2017-03-08T19:12:18.413
If you haven't solved this yet, consider leveraging off the
Worksheet_Changeevent to either 1) check if data validation has been broken, then re-instate it (if the formula isn't too complicated) or 2) cancel the paste and replace it with aPasteSpecial xlPatesValues. I haven't tested the second option to see ifCancelsetsApplication.CutCopyMode = Falsebut, If it doesn't, you may be able to hijack the paste to values. – Mark Fitzgerald – 2018-03-05T10:37:29.423