data validation question

Y

yongyong

Hi All,

I am trying to use data validation in Excel to restrict data entry to
13 characters per cell. So far, I have managed to make an error message
appear when the user enters anything other than 13 characters, which is
what I wanted.

However, this only works when the user keys in the data AND hit the
Enter key. If the user cuts and pastes data into the cell from
elsewhere, the data validation doesn't happen at all.

I'd appreciate any pointers to solve my problem. Thanks in advance!

cheers,
YY
 
F

Frank Kabel

Hi
to prevent such copy+paste action you'll need VBA. No chance to do this
with 'Data - Validation'. Try the following code (put this in your
worksheet module - not in a standard module)

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim sMessage As String

If Application.Intersect(Target, Range("A1")) Is _
Nothing Then Exit Sub
If Target.Cells.Count > 1 Then Exit Sub
On Error GoTo EndMacro
Application.EnableEvents = False
With Target
If Len(.Value) <> 5 Then
sMessage = "Entry has not a valid length"
MsgBox sMessage
Application.Undo
End If
End With

EndMacro:
Application.EnableEvents = True

End Sub
 
Y

yongyong

Thanks for replying! I'm afraid I'm still rather a newbie and do not ye
know what's a worksheet module as opposed to a standard module. Bu
this is a good starting point for me to begin my research :) Thank
again!

cheers,
Y
 
Top