Data Validation

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I'm looking for a bit of help, i would like to limit the max number of
caracters a cell can contain to 15, yes you can do this in data validation
but you can just paste something with greater than 15 caracters into the cell
and it will accept it, it only seems to block typing, thus is there a way to
maybe in an adjacent cell use the formula =len() and then using some
automatic macro say if there is a cell in the adjacent column with a number
over 15 bring up an error message? and get the user to change whats in the
cell, I'm not very good at coding hence i'm sure theres a way but dont know
it,

thanks,

Andy
 
Hi Momo,

If you are satisfied with an optical warning, you might
want to define a conditional format:

Select a cell, a range of cells, an entire row or column
and then goto Format / Conditional Formatting, select
under condition 1 "Formula Is", type in

=LEN(INDEX($1:$65536,ROW(),COLUMN()))>15

Push Format button then Pattern and select red, for
example, then hit ok.

HTH,
Bernd
 
Maybe something like the next code can help:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$C$3" Then
If Len(Target.Value) > 15 Then
Target.Value = Mid(Target.Value, 1, 15)
MsgBox "The length of this cell is limited to 15 characters"
End If
End If
End Sub

The code is placed in the Worksheet_Change event and (automatically)
executed when cell values are changed. In this example only the value of cell
C3 is validated, you can modify the range (i.e. by the row and column of the
Target object) as you like. If the cell content exceeds 15 characters, the
value is limited to 15 positions and a message is shown.

regards,
Tieme
 
Back
Top