Excel 2007 Shape text edit

[Jan Kucera]

Hi,
I have a shape on a sheet and would like to change a part of it's text
from a macro (actually VSTO C# project).

Is there any way?
Thanks, Jan

 

[Jan Kucera]

Well I found Sheet.Shape.TextFrame2, however, I have the text formated, 3
paragraphs, on italic, one right aligned and so on... and I want not to loss
the formating... is there any way to get individual lines?

Well and I also need to create such shape... (which could substitute the
editing - delete & create new)

Jan

 

[Wei Lu [MSFT]]

Hello Jan,

I suggest you to record a Macro in the excel to check what the VBA
statement you could use.

In my test, you could use the following to select the Shape and Edit the
Text.

ActiveSheet.Shapes("Rectangle 1").Select
ActiveSheet.Shapes("Rectangle 1").Select
Selection.Characters.Text = "Test 1" & Chr(10) & "" & Chr(10) & "Test"
With Selection.Characters(Start:=1, Length:=12).Font
.Name = "Verdana"
.FontStyle = "Bold"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With

I use the following to create a Shape:

ActiveSheet.Shapes.AddShape(msoShapeRectangle, 229.5, 266.25, 172.5,
134.25). _
Select

Hope this helps.


Sincerely,

Wei Lu
Microsoft Online Community Support

==================================================

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Jan Kucera]

Okay, I found Lines, Paragraphs, Runs and Senteces. What is the difference
between these?

This allows me to edit the such shape. How to create one? eg. there is no
Lines.Add and so on...

Jan

 

[Jan Kucera]

Hi, thank you for your reply. Unfortunately my actions with shapes do not
get recorded in the Excel macro... (in Word they do).

Well thank you for the tip with selection, I will keep that in mind.
However, I found the Lines collections returning textranges, so I can
probably set this on whole textrange object, which I consider better.
However, I don't now how to add a new Line...

My idea is to append vbCrLf & mynewline but I'm not sure if this is the
recommended way.

Jan

 

[Steve Rindsberg]

Okay, I found Lines, Paragraphs, Runs and Senteces. What is the difference
between these?

The text frame of a shape may contain one or more paragraphs.
.Paragraphs gives you access to the paragraphs collection.
.Paragraphs.Count will tell you how many paragraphs there are, for example.

Since the text may be set to wrap and since a paragraph might be more than one
line long, you can use .Lines to find out how many lines of text there are.

Sentences should be obvious.

Each time the formatting changes in any way, it starts a new Run. So if the
text were:

Something [bold on]like this[bold off] more or less.

there'd be three Runs.

This allows me to edit the such shape. How to create one? eg. there is no
Lines.Add and so on...

The macro recorder doesn't work and intellisense seems stupefied as well.
A pity. This should work:

Dim oSheet As Worksheet
Set oSheet = ActiveWorkbook.Sheets(1)

With oSheet
With .Shapes.AddShape(msoShapeRectangle, 0, 0, 200, 200)
.TextFrame2.TextRange.Text = "Like so"
End With
End With

 

[Steve Rindsberg]

Hello Jan,

I suggest you to record a Macro in the excel to check what the VBA
statement you could use.

I'm curious ... did you record this in Excel 2007?

If so, what build did you use?

 

[Jan Kucera]

Hi Steve,
thank you for clarification.

Dim oSheet As Worksheet
Set oSheet = ActiveWorkbook.Sheets(1)

With oSheet
With .Shapes.AddShape(msoShapeRectangle, 0, 0, 200, 200)
.TextFrame2.TextRange.Text = "Like so"
End With
End With

Yes, this works, but how do you create a shape with mixed formatting?
I am thinking something like...
Dim t1 as TextRange2, t2 as TextRange2
With t1
.Text = "Text1"
.ParagraphFormat.Alignment = msoAlignCenter
End With
With t2
.Text = "Text2"
.Font.Bold = msoTrue
End With

myShape.TextFrame2.TextRange = ... somehow t1 + t2
or TextRange.Add(t1) and TextRange.Add(t2)
....


Jan

 

[Steve Rindsberg]

Hi Steve,
thank you for clarification.


Yes, this works, but how do you create a shape with mixed formatting?
I am thinking something like...
Dim t1 as TextRange2, t2 as TextRange2
With t1
.Text = "Text1"
.ParagraphFormat.Alignment = msoAlignCenter
End With
With t2
.Text = "Text2"
.Font.Bold = msoTrue
End With

myShape.TextFrame2.TextRange = ... somehow t1 + t2
or TextRange.Add(t1) and TextRange.Add(t2)
....

If I understand what you want to do, you'd create the shape, add the text to it
and then format the text as needed (probably by using the .Characters property
to return a range of characters for each bit of text you want to format)

 

[Jan Kucera]

If I understand what you want to do, you'd create the shape, add the text
to it
and then format the text as needed (probably by using the .Characters
property
to return a range of characters for each bit of text you want to format)

Yep, if I create it, I know the number of characters, that makes sense
Last question, how do I - when just setting the Text property - distinguish
between new line and new paragraph?
If I use, vbCr, vbLf, vbCrLf, these all adds paragraph. But how to add line
break?

Jan

 

[Steve Rindsberg]

Yep, if I create it, I know the number of characters, that makes sense
Last question, how do I - when just setting the Text property - distinguish
between new line and new paragraph?
If I use, vbCr, vbLf, vbCrLf, these all adds paragraph. But how to add line
break?

I'm a PowerPoint geek so whatever I tell you might not fly in Excel.
But create yourself a text box with all of these characters and then run a
little code that steps through the text a character at a time, something like
<aircode_warning>this</aircode_warning>:

Dim sTemp As String
Dim x As Long

sTemp = ' the text from your shape's text frame

For x = 1 to Len(sTemp)
Debug.Print Asc(Mid$(sTemp,x,1)
Next

 

[Jan Kucera]

Well it seems to be a vbVerticalTab - Chr(11), "Not useful in Microsoft
Windows or on the Macintosh" )
Thank you for your help,
Jan