Do you want to limit it 10 characters or 2 characters?
=left(a1,10) is 10 (I use a comma in my USA version)
target = left(target,2) is only 2 characters.
In either case, maybe this will help:
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
I checked all of column B, just the cells in C3:D9, all of column X and all the
cells in columns Q:S.
You can modify that the way you need.
I'd add some more checks--ignore numbers, dates, anything you need to ignore.
abouassi wrote:
hello, please help!!!
briefly,i have to enter data in an excel worksheet,and after that, i
have to cope everything and paste it in another program( AS/400 this is
not important) ,and the problem is that the AS/400 needs to have
specific number of characters in every column, what i want to do , is
to write a function, or a small program in VBA so that i can identify
the number of characters in every column..
1.going to datavalidationtext length ... doesnt work, cause i want it
to erase automatically what is beyond the length needed
2. the function LEFT(A1;10) for example is not working in my excel
2002, i don't know why, maybe if it works it would be great,but it
seems that excel is not recognising it
3.in VBA
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column < 2 Then Exit Sub
Target = Left(Target, 2)
End Sub
is working,but i when i paste more than one cell in more than one cell
in the column 2 (in this example) it is giving me an error, moreover,i
don't know how to write it in order to program many columns at the same
time...
thanks for help
--
abouassi
------------------------------------------------------------------------
abouassi's Profile: http://www.excelforum.com/member.php...o&userid=37591
View this thread: http://www.excelforum.com/showthread...hreadid=572165
--
Dave Peterson