limiting characters in a cell
Just to add to Ken's response.
You may want to use the .text property (for dates???).
myCell.NumberFormat = "@" 'added to format cell as Text
myCell.Value _
= Left(myCell.Text & Space(myLengths(iCol)), _
myLengths(iCol))
=====
And I don't know the answer about the MSWord question. Maybe running
Table|convert after pasting would be sufficient?????
Ken Johnson wrote:
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
--
Dave Peterson
|