Excel Add Text Box

HCL Domino Domino Forum
1

Greetings,

Has anybody out there been able to generate a textbox on an MS Excel Chart? I have created a many excel reports in the past, but never had a requirement to enter a textbox until now. As in the past, I have created the chart and used excel to record a macro, took that code into LS and translated, but it keeps booming out! Not sure what I’m missing here. The code errors out at Selection.Characters.Text = “Unique Full Application Submissions” line.

Here’s the culprit:

xlChart.PlotArea.Select

Selection.Left = 9

Selection.Top = 26

ActiveChart.Axes(1).Select  ' xlCategory

Selection.TickLabels.AutoScaleFont = True

With Selection.TickLabels.Font

    .Name = "Arial"

    .FontStyle = "Regular"

    .Size = 9

    .Strikethrough = False

    .Superscript = False

    .Subscript = False

    .OutlineFont = False

    .Shadow = False

    .Underline = -4142  ' xlUnderlineStyleNone

    .ColorIndex = -4105  ' xlAutomatic

    .Background = -4105  ' xlAutomatic

End With

xlChart.Shapes.AddTextbox(1, 251, 406, 193, 29).Select

Selection.Characters.Text = "Unique Full Application Submissions"

Selection.AutoScaleFont = False

With Selection.Characters(Start:=1, Length:=35).Font

    .Name = "Arial"

    .FontStyle = "Regular"

    .Size = 10

    .Strikethrough = False

    .Superscript = False

    .Subscript = False

    .OutlineFont = False

    .Shadow = False

    .Underline = -4142  ' xlUnderlineStyleNone

    .ColorIndex = -4105  ' xlAutomatic

End With

With Selection

    .HorizontalAlignment = -4108  ' xlCenter

    .VerticalAlignment = -4160  ' xlTop

    .ReadingOrder = -5002  ' xlContext

    .Orientation = -4128  ' xlHorizontal

    .AutoSize = False

End With
2

Subject: Getting There!!!

All,

I have learned that the issue is not so much with creating/adding the text box. At least, I noticed my code gets beyond creating the textbox. From what I see, the issue appears to be with adding the text to the text box itself (see line below).

    • ERROR LINE - -

.Characters.Text = “Unique Full Application Submissions”

I have tried numerous combinations in hopes of resolving this, but with no success.

With xlChart

.Characters.Text = “TEST”

End With

xlChart.Shapes.Characters.Text = “TEST”

xlChart.TextFrame.Characters.Text = “TEST”

Just to mention a few, but none have worked…

Any thoughts or ideas???

Ric

3

Subject: Issue Resolved…

All,

The whole point in my posting was so that I could display text relevant to my chart report at the bottom of the chart, all while displaying a title at the top.

To resolved this issue, I ended up making extra use of the ChartTitle Method, specifically in this case the x axis portion which allowed me to reach my finish line.

Regards All,

Ric

4

Subject: Found a solution!

I know it has been some time since you have posted this query. I faced the same issue now and it was really strange that I could not find any solution to this online. I was also going to settle with changing the Chart Title. But, luckily, I bumped on the solution and thought of sharing.

I tried various combinations of syntax and finally this worked.

Print |oChart.Shapes.AddTextbox(1, 40, 450, 351, 20).TextFrame2.TextRange.Characters.Text = “| +”**testing"+ |" ;|

Note:-

  1. creating textbox and setting the text value has to be in single line

  2. add the textbox before starting to set the chart properties

  3. Instead of using ‘msoTextOrientationHorizontal’ as parameter, use 1