Use VBA to Validate data entered or pasted into a range

[stanlelma]

Hi,

I want to prevent users from entering data more than 20 characters in length into a named excel range.

This can be achieved with normal Excel Data Valiadtion set-up but my problem is that when users paste data into the range, which will do often due to the high number of updates required, the validation is not applied. It looks like VBA code could be used but I am not an expert with that.

Could somebody please provide me with a VBA code example?

Thanks in advance.

[venkat1926]

use an EVENTG CODE
rightclick the sheet tab and click view code in the window that comes up copy this code

Please Login or Register  to view this content.

the relevant cell is A1 in the sheet. if some thling esle you have to change the line in the code
If Target.Address <> "$A$1" Then Exit Sub

now enter any entry or copy paste in THAT cell and see what happens.

[stanlelma]

Thanks this worked for cell A1 but how can I apply this to Column A? When I changed the code from If Target.Address <> "$A$1" Then Exit Sub to If Target.Address <> Range("$A:$A") Then Exit Sub and tested again it failed. Maybe I am selecting the range incorrectly.

[ragulduy]

There are a few methods, you could do:

Please Login or Register  to view this content.

or if you have a particular range you want to apply it for you could use INTERSECT():

Please Login or Register  to view this content.

[stanlelma]

Thanks again. However it is not exaclty what I need. The problem is that when I paste a number of cells into the range that contain some data with more than 20 characters no error message is displayed. Sorry to be so particular but this is exacly what is likely to happen. i.e. Many records pasted into a column with the possibility of only one cell data being too long (>20).

[ragulduy]

You would need to loop through the different cells and test each one individually. To only adjust cells that have more than 20:

Please Login or Register  to view this content.

to clear everything you paste if one is more than 20:

Please Login or Register  to view this content.

[stanlelma]

This is really excellent. Thank you so much.
I would like two refinments if possible.
1. To reference the Cell Address in the message if entry >20. example: You have entered more than 20 characters in cell A125
2. To truncate that entry to 20 characters if entry >20

Thanks again

[ragulduy]

Please Login or Register  to view this content.

[stanlelma]

Thanks, However this only work for me when I remove cL = Left(cL,20)

[ragulduy]

Please Login or Register  to view this content.

?

[stanlelma]

Awesome! Thanks