Block paste possibility

[mcs51mc]

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

[Mike]

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
>
>

[mcs51mc]

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

[Dave Peterson]

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]

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