limiting characters in a cell

[abouassi]

hi, please help
i need to associate for every column different number of characters
using a vba code
example :

column A contains maximum 20 characters
column B contains maximum 2 characters
column C contains maximum 10 characters
....
.....
....and so on, i have about 15 coluumns, i can do it manually

these ways dont work:

1.data->validation->text length->.... doesnt work, cause i need to put
more than a X elements, and i want the code to erase all the elements
after X
example, if the max number is 6 if i put "i drink coffee" i want to
have "i drin" without any alert notes
2. the function =left(A1,6) might be a could idea, but i don't know why
my excel 2002 is not recognising it
3. here i have examples of vba codes, but they have problems

a.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim myRngToCheck As Range
Dim myCell As Range

Set myRngToCheck = Me.Range("b:b,c3:d9,x:x,Q:S")

If Intersect(Target, myRngToCheck) Is Nothing Then
Exit Sub
End If

On Error Resume Next 'just keep going!
Application.EnableEvents = False
For Each myCell In Intersect(Target, myRngToCheck).Cells
myCell.Value = Left(myCell.Value, 10)
Next myCell
Application.EnableEvents = True
On Error GoTo 0

End Sub
its problem is that it doesnt allow me to associate a different length
for a different column
and the other code have the same problem too

b.
Private Sub Worksheet_Change(ByVal Target As Range)
Select Case Target.Column
Case 2, 3, 4, 5, 8, 9
Application.EnableEvents = False
Dim rngCell As Range
On Error Resume Next
For Each rngCell In Target
rngCell.Value = Left(rngCell.Value, 10)
Next rngCell
Application.EnableEvents = True
Case Else
End Select
End Sub

in the second one, i'm able to choose the columns 2,3,4,5,8,9 but i'm
not able to associate different length for every one( here in this
example, 10 is their length)



thanks for who can help, giving a new code or even to change in these
ones,

 

[AAA]

please note that if the characters entered are less than the associated
length,i want it to be filled with empty space(if possible) i'll
explain in few words my problem:

i have to enter data in excel to a worksheet with specific
characteristics that i'm searching for, cause after that i'll copy
them, and i'll paste them in another program, but every column in this
data have to have a specific width(number of characters) if the data
entered is more than the associated width, all the characters to the
right have to be deleted, and if the data enetered have smaller number
of characters,empty space have to be filled , thanks
(e-mail address removed) a écrit :