limiting characters in a cell
Hi AAA,
a possible solution to your first problem is to either manually format
your columns as Text or insert...
myCell.NumberFormat = "@"
into the code so that numbers are actually text and the spaces are
added.
If you decide to do the formatting with the code then your new code
will be...
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, 2, 3, 4) 'A, E, L
myLengths = Array(10, 3, 20, 10)
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.NumberFormat = "@" 'added to format cell as Text
myCell.Value _
= Left(myCell.Value & Space(myLengths(iCol)), _
myLengths(iCol))
Next myCell
Application.EnableEvents = True
On Error GoTo 0
End Sub
Sorry I don't know about your other problem.
Ken Johnson
|