Prevent invalid data when pasting from Word directly onto cell?

[Stacey]

I have a spreadsheet that a customer uses to provide me with catalog data for
our application. Despite direction, he consistently provides data that
exceeds maximum field lenghts for our database. I have tried conditional
formatting and restricting the data based on max length but he is often
copying the text from Work and just pasting it idirectly nto the Excel cell
(not using the text bar). Neither formatting or restricting work in this
scenario. Any ideas on how to prevent him from being able to paste in
invalid data?

 

[Sean Timmons]

Data - Validation

Text length from the drop-down

enter a max number...

You can make an message too!

 

[Stacey]

Sean, that only works if you actually click into the cell, then paste the
data. If you copy text from word and just select the cell and paste it won't
perform the data validation.

 

[Gord Dibben]

Try this event code.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 4 Then
With Application
.EnableEvents = False
If Len(Target) > 14 Then
MsgBox "Listen up Knob! Maximum of 14 characters!!"
Target.Value = ""
End If
.CutCopyMode = False
.EnableEvents = True
End With
End If
End Sub

Since you don't provide any range, I have used all of Column D and a max.
number of characters of 14. Edit to suit.

This is sheet event code. Right-click on the sheet tab and "View Code"

Copy/paste into that module. Edit to suit and Alt + q to return to the
Excel window.


Gord Dibben MS Excel MVP