Dropdown validation and paste

[Guest]

Hi,
I've inherited an Excel workbook that has a large number of dropdowns used
for data validation. The sheet is protected to prevent users from being able
to paste values into the workbook. I'd like to allow users to perform a
paste operation, and cause the validation associated with each of the
drop-down lists to fire so that the paste can be prevented if the data input
is invalid. This needs to work on Excel 97 and higher.
Is this possible?

 

[Guest]

Validation doesn't fire unless you manually enter a value in a cell. So your
basic approach is not supported. Maybe have them do their work on an
unprotected duplicate sheet, then validate that input with code and update
the protected sheet/provide feedback as appropriate.

 

[Guest]

Thanks for your reply. How annoying! Is there any way to either:
1) Expressly call the cell validator from the Worksheet_Change event?
2) Access from VBA the range of cells that the validator should be
validating against after the cell change event has fired?

 

[Tom Ogilvy]

Well yes, the change event can execute most code. So you can access the
Validated range with code triggered by the change event as long as the user
doesn't disable macros overtly or without knowledge because of a high
security setting.