Just to add...

If you want to try this, rightclick on the worksheet tab that should have this
behavior. Select View code and paste that code into the codewindow.

Remember to change this line:
Set RngToCheck = Me.Range("a1,b9,c12")
to the range you need.

Dave Peterson wrote:

You may want to consider moving your validation rules into a worksheet_event.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim RngToCheck As Range
Dim myCell As Range
Dim ImportantRng As Range

Set RngToCheck = Me.Range("a1,b9,c12")
Set ImportantRng = Intersect(Target, RngToCheck)

If ImportantRng Is Nothing Then Exit Sub

On Error Resume Next 'just keep going
Application.EnableEvents = False
For Each myCell In ImportantRng.Cells
If Len(myCell.Text) 20 Then
MsgBox "Cell: " & myCell.Address(0, 0) & " has been changed!"

myCell.Value = Left(myCell.Text, 20)
'or
'myCell.ClearContents
End If
Next myCell
Application.EnableEvents = True

End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

If you want to read more about these kinds of events:

Chip Pearson's site:
http://www.cpearson.com/excel/events.htm

David McRitchie's site:
http://www.mvps.org/dmcritchie/excel/event.htm

=====
Be aware that if macros/events are disabled, then this technique will also fail.

mcs51mc wrote:

Hi there,

I made a protected wks so that a user can access only some cells. In
some of these prodected cells I add validation on "Text length"
(maximum 20 characters). So far so good, user can enter maximum 20
characters.

But with "paste" one can enter all the characters from the clipboard


So how can I prevent the user to "paste" something in a cell?

Thanks a lot for any reaction
Alain

--
mcs51mc
------------------------------------------------------------------------
mcs51mc's Profile: http://www.excelforum.com/member.php...o&userid=28645
View this thread: http://www.excelforum.com/showthread...hreadid=483104

--

Dave Peterson

--

Dave Peterson