Data Validation: Define Maximum Limit of Characters/ Digits in Cell

You have discovered the fatal flaw of data validation. It is over-written when pasting other cells into the validation cell. Unless you lock the cell and apply worksheet protection there is nothing that can be done to prevent it.

thanks for info, but I have to make a spreadsheet with below rules: (suppose in Column A)
— a range of minimum 4 digits and maximum 8 digits in every cell of column A, and no-one can enter any other value by manually & pasting. if someone tries to enter any other value (digits) besides above limitation, should be informed.
— we have to put values in the column A by manually & pasting from the other cells or locations.

...and if I protect the sheet how'd I enter the values? or wht options I should have ticked (Allow) under the protect-sheet option? plz help! :O

I've often used code along these lines to get around the data validation issue - hopefully you may find it useful.

In the code module of the worksheet on which you want the validation to occur, type the following code:

Please Login or Register  to view this content.

Thanks for ur efforts dear!

I've just pasted ur codes, but nothing is working fine. If I paste any digit/ value which is <4 digits or >8 digits gets pasted easily in the column 'A'.

Sorry about that Sunoffice - you could try amending the code as follows:

Please Login or Register  to view this content.

Having said that - both these bits of code have the same effect - not quite sure why it's not working for you, it works fine when I try it. You're not pasting into Rows 1, 2 or 3 by any chance? These are explicitly excluded in the above code.