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

Maybe add cells with =len(a1) and use Format Conditional Formatting to
change text to red if over 20? You must have people that want to paste a
block of data rather than manually typing. Over 20 must rarely occur.
Forcing that not to paste will drag their productivity down. So a compromise
might be better. That way they can quickly paste their block of data which
keeps their accuracy high. They can focus only on ones that exceed 20
characters.

You could expand it further with displaying the maximum length for the cells
that they can edit. Conditional formatting that cell to display bold red.
Then tell them they are guaranteed no Christmas bonus if they break the rules.

"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

Thanks Mike for your concer about the productivity of my users :)

So instead of preventing users to paste data into a cell how can I fire
the validation event?
Remember there is a validation on "Text length" maximum 20 characters
in these cells. This way when the user hit the Enter key and there is
more than 20 characters in the cell he already get an error window with
some help.

I presume the validation event is fired by the enter key.
How can I tell Excel to also fire it with for example the
cell.value.change event??

Alain


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

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

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