How to modify contents of TextFrame?
 

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

How to modify contents of TextFrame?
 

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

--
Regards,
Tom Ogilvy

Robert Stober wrote in message
...
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

How to modify contents of TextFrame?
 

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

"Robert Stober" wrote in message
...
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

How to modify contents of TextFrame?
 

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


"Paul" wrote in message
...
Is your shape locked and protected?
or
Were you in break mode in the middle of another procedure?

"Robert Stober" wrote in message
...
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

How to modify contents of TextFrame?
 

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
"Robert Stober" wrote in message
...
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

How to modify contents of TextFrame?
 

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
--------------------------------------------------------------------------------------------------------------
Be advised to back up your WorkBook before attempting to make changes.
--------------------------------------------------------------------------------------------------------------
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

How to modify contents of TextFrame?
 

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

wrote in message
...
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
--------------------------------------------------------------------------
------------------------------------
Be advised to back up your WorkBook before attempting to make changes.
--------------------------------------------------------------------------
------------------------------------
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

How to modify contents of TextFrame?
 

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
--------------------------------------------------------------------------------------------------------------
Be advised to back up your WorkBook before attempting to make changes.
--------------------------------------------------------------------------------------------------------------
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