writing strings > 255 characters to Text Box

[StevenD72]

I have this code that works perfect in Excel 2007, but when I went to use it
in Excel 2003 in does not work. It appears that excel 2003 is only allowing
me to write 255 characters at a time and that is where the problem lies. Can
some one help me fix this so that it writes to the same text box in "chunks"
of 255 characters OR make this work as a whole.

Thanks in Advance.

-----------------------------------------------
Sub Fill_Letter()

Dim ws As Worksheet
Dim ws2 As Worksheet
Set ws = Worksheets("Billing Letter")
Set ws2 = Worksheets("Billing Statement")

'Get Info from Billing Statement

Inc_Date = ws2.Cells.Range("D1").Value
Inc_No = ws2.Cells.Range("L1").Value
Inc_Addrs = ws2.Cells.Range("C8").Value
Inc_City = ws2.Cells.Range("B9").Value
Con_Name = ws2.Cells.Range("D12").Value
Con_Comp = ws2.Cells.Range("E11").Value
Con_Addrs = ws2.Cells.Range("C13").Value
Con_City = ws2.Cells.Range("B14").Value
Con_State = ws2.Cells.Range("H14").Value
Con_Zip = ws2.Cells.Range("J14").Value
FD = ws2.Cells.Range("D2").Value
TotalChg = ws2.Cells.Range("L40").Value

'Place Header on page
ws.DrawingObjects("HeaderBox").Select
Selection.Characters.Text = "THIRD DISTRICT CHIEF'S ASSOCIATION" & vbLf _
& "M.A.B.A.S DIVISION 24" & vbLf _
& "HAZARDOUS MATERIALS RESPONSE TEAM" & vbLf _
& "BILLING STATEMENT"

'Place main letter on Page
ws.DrawingObjects("Textbox2").Select

Sdate = FormatDateTime(Date, vbLongDate) 'Gets Todays Date

StrTmp = Sdate _
& vbLf & vbLf _
& Con_Name & vbLf & Con_Comp & vbLf _
& Con_Addrs & vbLf & Con_City & ", " _
& Con_State & " " & Con_Zip & vbLf & vbLf _
& "This statement is for services provided by the MABAS 24 Hazardous
Materials " _
& "Response Team for the following incident: " & Inc_No _
& " on " & Inc_Date & " at:" & vbLf & vbLf & Inc_Addrs _
& vbLf & Inc_City & vbLf & vbLf _
& "The Hazardous Materials Team responded at the request of the " & FD _
& " Fire Department and provided technical support. " _
& "These charges are for services provided by the MABAS 24 Hazardous
Materials " _
& "Response Team only. Other charges may be pending from municipalities
or " _
& "clean-up companies if required." & vbLf & vbLf _
& "Total charges for services provided by HazMat Response Team: " _
& "$" & TotalChg & vbLf & vbLf _
& "See attached statement of services." _
& vbLf & vbLf

StrTmp = StrTmp _
& "Please remit to:" & vbLf & vbLf _
& "Third District Chief’s Association" _
& vbLf & "C/O Deb Hoiden" _
& vbLf & "Flossmoor Fire Department" _
& vbLf & "2800 Flossmoor Road" _
& vbLf & "Flossmoor, IL 60422" _
& vbLf & vbLf _
& "Any questions, please contact me at 708-362-0561, or email
(e-mail address removed)." _
& vbLf & vbLf _
& "Sincerely," & vbLf & vbLf & vbLf & vbLf _
& "FF/PM Scott Stegenga" & vbLf & "Billing Agent"


Selection.Characters.Text = StrTmp
' MsgBox (StrTmp)
End Sub

 

[Jon Peltier]

The way I had to use it was to divide the text into chunks, put the last
chunk into the textbox, then work backwards, inserting each chunk before the
first character in the textbox. This Sub is what I used:

Sub InsertTextIntoTextbox(shpTxt As Shape, strTxt As String)
Dim iLen As Long
Dim iCount As Long
Dim iIndex As Long
Dim sSplit() As String

Const dLen As Long = 250

iLen = Len(strTxt)
iCount = iLen \ dLen
ReDim sSplit(0 To iCount)

For iIndex = 0 To iCount
sSplit(iIndex) = Mid$(strTxt, 1 + iIndex * dLen, dLen)
Next

shpTxt.TextFrame.Characters.Text = sSplit(iCount)

For iIndex = iCount - 1 To 0 Step -1
With shpTxt.TextFrame.Characters(1, 1)
.Insert sSplit(iIndex) & .Text
End With
Next

End Sub



I called the Sub like this:

InsertTextIntoTextbox ActiveSheet.Shapes("Text Box 1"), myLongString


- Jon

 

[StevenD72 via OfficeKB.com]

Thanks Jon

Worked perfectly.

The way I had to use it was to divide the text into chunks, put the last
chunk into the textbox, then work backwards, inserting each chunk before the
first character in the textbox. This Sub is what I used:

Sub InsertTextIntoTextbox(shpTxt As Shape, strTxt As String)
Dim iLen As Long
Dim iCount As Long
Dim iIndex As Long
Dim sSplit() As String

Const dLen As Long = 250

iLen = Len(strTxt)
iCount = iLen \ dLen
ReDim sSplit(0 To iCount)

For iIndex = 0 To iCount
sSplit(iIndex) = Mid$(strTxt, 1 + iIndex * dLen, dLen)
Next

shpTxt.TextFrame.Characters.Text = sSplit(iCount)

For iIndex = iCount - 1 To 0 Step -1
With shpTxt.TextFrame.Characters(1, 1)
.Insert sSplit(iIndex) & .Text
End With
Next

End Sub

I called the Sub like this:

InsertTextIntoTextbox ActiveSheet.Shapes("Text Box 1"), myLongString

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______

I have this code that works perfect in Excel 2007, but when I went to use
it

[quoted text clipped - 84 lines]

' MsgBox (StrTmp)
End Sub