Individual Validation for multiple values in single cell

[quest4rachit]

Hi,

We have a excel sheet in which in one of the cell we are storing multiple
Email id's .
Now we want to have lenght validation on each Email id such that each email
id's text lenght must not exceed 150 character lenght.We can apply validation
to text of cell, but how we can apply individual validation to each email id.

 

[Rick Rothstein \(MVP - VB\)]

Can you provide a little more detail? For example, how are the IDs delimited
(line feeds, commas, tabs, something else)? When you say "validation", do
you mean after an entry is completed via a formula, Data Validation, via a
macro, something else? How do you want to be notified of any IDs exceeding
150 characters? I'm not sure if everything I asked you about is doable, but
it would definitely help if you told us what you are expecting to do.

Rick

 

[quest4rachit]

Hi Rick,

The ID's are delimited by either by ; or by comma ,.Validation means in that
template informtaion is entered manually and when the entry is made in email
id cell (multiple email id) then if those email id's are more then 150
letters each then an error message should appear saying you cant have email
id lenght more then 150 letters and that entry will be cancelled.

 

[Rick Rothstein \(MVP - VB\)]

Okay, I think you are asking for more than a simple cell validation can
handle; so I think you will need to use VB event code to do what you want.
Go to the worksheet you want this functionality on and right click its tab
(located at the bottom of the worksheet), select View Code from the popup
menu that appears, then Copy/Paste this code into the code window that
appeared when you did that...

'*************** START OF CODE ***************
Dim Contents As String

Const CellAddress As String = "B2:B5,E5"

Private Sub Worksheet_Change(ByVal Target As Range)
Dim X As Long
Dim C As Range
Dim IDs() As String

If Not Intersect(Target, Range(CellAddress)) Is Nothing Then
On Error GoTo Whoops
Application.EnableEvents = False
IDs = Split(Replace(Target.Value, ";", ","), ",")
For X = 0 To UBound(IDs)
If Len(IDs(X)) >= 15 Then
MsgBox "This Email ID..." & vbLf & vbLf & IDs(X) & _
vbLf & vbLf & "contains 150 or more characters. " & _
"That is too many characters and is not allowed", _
vbCritical, "Entry Too Long"
Target.Value = Contents
Exit For
End If
Next
End If
Whoops:
Application.EnableEvents = True
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range(CellAddress)) Is Nothing Then
Contents = Target.Value
Else
Contents = ""
End If
End Sub
'*************** END OF CODE ***************

It was not clear to me from your postings whether there would be only one
cell ever that need this validation or whether there could be more than one;
so I set the code up to handle either situation. If you want only one cell,
set the CellAddress constant statement to that address; for example...

Const CellAddress As String = "E5"

Or, if you have more than one cell, use their addresses instead...

Const CellAddress As String = "E5,H7,J10"

Or, if you have a range of cells where the validation should apply, say
Column B, then use this instead...

Const CellAddress As String = "B2:B20"

or you can mix cells and ranges...

Const CellAddress As String = "E5,B2:B20,H7"

Now go back to the worksheet... any cell in the range you specify will be
checked for entries between semi-colons or commas being 150 characters or
more in length. If they meet that criteria, a message box will be displayed
advising of the 'error' and, after the MessageBox's OK button is pressed,
the cell will be returned to its former contents.

Rick