How to modify contents of TextFrame?

[Robert Stober]

Hi,

I've got a shape that has an asscoiated TextFrame that contains some text.
I'd like to replace that text using VBA code. But I can't get it to work.
based on the oputput of the macro recorder, this should work:

With Sheets("Start").Shapes(7).TextFrame
.Characters.Text = "AAAAA"
End With

But it doesn't. I've also tried to use delete and insert methods of the
characters class, but these methods seem to belong to a different charaters
class than the one provided by the Shape.TextFrame object.

Does anyone know how to do this?

Thank you very much,

Robert Stober

 

[Tom Ogilvy]

your code worked fine for me. Perhaps your shape doesn't support text.

 

[Paul]

Is your shape locked and protected?
or
Were you in break mode in the middle of another procedure?

 

[Robert Stober]

Paul,

Yes, actually the entire page is protected, but I think the macros should
still be able to operate on... wait I see DrawingObjects:=True... I'll
change that and then let you know...

' the cells on the "Start" worksheet cannot be selected, but macros will
work
Sheets("Start").Protect DrawingObjects:=True, Contents:=True, _
Scenarios:=True, UserInterfaceOnly:=True

Thank you,

Robert

 

[Robert Stober]

Tom and Paul,

I found the problems(s) and thought that I'd pass them on:

1. Yes, my page was protected. I overcame this by adding some unprotect
code, then reprotected making sure to set UserInterfaceOnly:=True so my code
can still operate on the sheet.

Sheets("Start").Unprotect

.... chart generating code here

Sheets("Start").Protect DrawingObjects:=True, & _
Contents:=True, & _
Scenarios:=True, & _
UserInterfaceOnly:=True

2. I've determined that there's a 255 character limit on shape.TextFrame
objects. So I switched to a TextBox thinking thatI'd be able to put more
characters in there. But I soon found out that a "textBox is really a shape,
and so I still can't get more than 255 characters in it.

But interestingly, I also learned that there is only one Characters class -
it's just *how* I call it that makes the difference. For instance if I code
this:

With Sheets("Start").Shapes(7).TextFrame
.Characters.Text = "mytext" ' better not exceed 255 characters
End With

I can only use the "Text" property. But if I code:

Sheets("Start").Shapes(7).Select
With Selection
' works now, but still no more than 255 characters!
.Characters.Insert String:="mytext"
End With

Maybe there is two Characters classes, and in the latter form I'm using the
one that inherits from Range, and in the former I'm using the one for
TextFrame. I'll admit to being *slightly* confused on this...

The bottom line:

How can I get more than 255 characters of text onto my worksheet? I'm
willing to use *any* technique...

Thank you,

Robert Stober

 

[pfsardella]

This is a generic routine that I use to fill TextBoxes with strings >
255 characters. Just pass the string and a reference to the shape.

Sub FillTextBox(strText As String, shpTB As Shape)
'' Generic routine to fill a text box with more than 255 characters.

Dim intI As Integer
Dim strChunk As String

intI = 1

Do While intI < Len(strText)
strChunk = Mid(strText, intI, 250)
shpTB.TextFrame.Characters(intI).Insert String:=strChunk
intI = intI + 250
Loop

End Sub

Sub Test
'' Assumes that there is already a Text Box 1.

Dim strPctPos as String
Dim shpSS as Shape

strPctPos=String(1000, "*")
Set shpSS = ActiveSheet.Shapes("Text Box 1")
Call FillTextBox(strPctPos, shpSS)

End Sub

HTH
Paul

 

[Robert Stober]

Paul,

This is GREAT stuff! I had just about given up on it...

Will the TextBox have a scrollbar so the user can access all the text?

Thank you,

Robert

 

[pfsardella]

Watch for linewrap. Adds a TextBox with scrolling capability. This
adds a TextBox from the Control Toolbox. Controls added to worksheets
from the Control Toolbox are more phinicky(?), but I haven't have much
problem with TextBoxes.

I add them programmatically, but you don't have to. With those from
the Control Toolbox (versus the Forms Toolbar), you don't have to deal
with the 255 character limitation (I had a link from Googel that
explained the differences but I can't find it). I usually delete them
when I'm done loading a file or whatever into the TextBox.

Sub DeleteTextBox(Optional varName As Variant)
'' Deletes a text box from the Control Toolbar.

ActiveSheet.Shapes("MyTextBox").Delete

End Sub

------------------------------------------------------------------------------------------------------

Sub FileIntoTextBox()
'' Adds a text box which has a scroll bar.

Dim txtBox As MSForms.TextBox
Dim oleTB As OLEObject
Dim wSht As Worksheet
Dim wBk As Workbook

Application.ScreenUpdating = False

[a1].Select

Set wBk = ActiveWorkbook
Set wSht = wBk.ActiveSheet

Set oleTB = wSht.OLEObjects.Add(ClassType:="Forms.TextBox.1",
link:=False, _
DisplayAsIcon:=False, Left:=1, Top:=1, Width:=600,
Height:=150)
With oleTB
.Visible = True
.Name = "MyTextBox"
End With
Set txtBox = oleTB.Object

With txtBox
.Top = ActiveWindow.VisibleRange.Top + 100
.Left = ActiveWindow.VisibleRange.Left + 100
.MultiLine = True
.ScrollBars = 2
.EnterKeyBehavior = True
.Font.Name = "Courier New"
.Font.Size = 8
.SelStart = 1
End With

End Sub

Tested using Excel 97SR2 on Windows 98SE,

HTH
Paul