limiting characters in a cell
This worked ok for me:
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim myRngToCheck As Range
Dim myCell As Range
Dim myLengths As Variant
Dim myCols As Variant
Dim iCol As Long
myCols = Array(1, 5, 12) 'A, E, L
myLengths = Array(10, 2, 5)
Set myRngToCheck = Me.Columns(myCols(LBound(myCols)))
For iCol = LBound(myCols) + 1 To UBound(myCols)
Set myRngToCheck = Union(myRngToCheck, Me.Columns(myCols(iCol)))
Next iCol
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
'the arrays are 0 based, so we subtract 1 from the match
iCol = Application.Match(myCell.Column, myCols, 0) - 1
myCell.Value _
= Left(myCell.Value & Space(myLengths(iCol)), myLengths(iCol))
Next myCell
Application.EnableEvents = True
On Error GoTo 0
End Sub
AAA wrote:
hi,i'm sorry for working in both forums, infact , this is the first
time i use it, and i didn't know that they are the same...
everything is working great, but forgive me, i still have a small
problem that i've just noticed. is it possible if the number of
characters in a cell is less than a specifique number , to fill the
rest with an empty space...
example
column A contains 7 characters, if i insert
"hello world" i have to get "hello w" (without the quotations)
and if i insert
"be" i get "be "
thanks a lot people, you are great
Ken Johnson a écrit :
Hi,
This operates on all pasted cells in columns 2,3,4,5,8 and 9. Just edit
to suit your needs
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
Ken Johnson
--
Dave Peterson
|