Possible to lock data validation and yet permit cell editing?

**danknight4jc** · 04-22-2009, 09:14 AM

What I'm trying to achieve is to be able to lock the text length of a column so that users can enter text data into that column but not exceed the allowable length for our purposes. I've found a way to use auto loading macros to make a popup if they exceed the set length. However, that is useless for our purposes because 1) pasting data into the cells as opposed to manually entering data overrides the validation just as it does when you set it with the data validation menus, 2) it requires that macros be activated in the end user's computer which we cannot guarantee will happen. If you lock in the data validation by protecting the worksheet, the end user can't enter data. Do you know of a way to lock in the data validation without using active controls like macros, and yet allow the end user to enter data into the cells. (Perhaps a solution would be to somehow prevent the pasting of data into a cell forcing the data validation to be utilized?) Thanks in advance for any insight or advice you might have.

**Bjornar** · 04-22-2009, 03:09 PM

I don't think you will find a solution to this problem without using macros.
Data validation works fine as long as the user don't paste into the cells.

The solutions I have tried is:
1. Prevent pasting into the cell
2. Convert all pasting actions into "paste special - value" action.
3. Let the user paste as normal, but reapply the validation and formatting of the cell after the entry is done. This validation will not kick in after the pasting is done, but I have used conditional formatting to warn the user about the error.

But unfortunately none of these work without macros activated.

Without macros you could use a formula in a protected cell that validates the content of the input cell. This will not prevent pasting bad values, but you will be able to display a warning to the user.

**danknight4jc** · 04-22-2009, 04:16 PM

Thanks, I really appreciate your input. I already came up with the solution of having another cell change color and come up with a warning message indicating that the field length had been exceeded. But I would be interested if you know macro code that would convert all "pastes" to "paste special" and/or reactivates the data validation. I suppose that would involve some sort of "click event" or "change event". I found syntax in excel help for such things, but could not make enough sense of their general formulas to convert it into actual code that works. Thanks again for taking time to reply.

**Bjornar** · 04-22-2009, 04:41 PM

Here is a link to an article on catching the paste operation that might give you some ideas:

http://www.jkp-ads.com/articles/catchpaste.asp

I was also involved in an discussion on the subject of preserving validation about a year ago. You will find it here:

http://www.dailydoseofexcel.com/arch...te-operations/