How to return a reference to a text object inside the Excel chart object model with VBA?

Jamie Garroch
Jamie Garroch used Ask the Experts™
on
Is it possible to return a reference to the selected text within a chart object placed on a PowerPoint slide?

In the example below, the text for the chart title object is selected.

Chart text selection
This returns true in the PowerPoint VBE Immediate window:

?ActiveWindow.Selection.Type=ppSelectionText

Open in new window


But both of these return the error "The specified value is out of range.":

?ActiveWindow.Selection.TextRange.Text
?ActiveWindow.Selection.TextRange2.Text

Open in new window


Is there a way to return a reference to these types of formatted text objects within the Excel chart OM?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
NorieAnalyst Assistant

Commented:
Have you tried starting off with something like this?
Dim txt As Object

    Set txt = ActiveWindow.Selection

Open in new window


That should give you a reference to the selected object that you can then examine in the Locals Window to see how you can access the properties you want.
Jamie GarrochSenior Technical Consultant at BrightCarbon

Author

Commented:
Thanks Norie. I tried the following and I get the same error when text inside a chart on the slide is selected but not when text inside a text box on the slide is selected:

' To test:
' 1. Insert a chart on a PowerPoint slide
' 2. Select the text inside the chart title object
' 3. Run this macro
Sub TestChartTitleSelection()
  Dim oObj As Object
  
  Set oObj = ActiveWindow.Selection
  
  Debug.Print TypeName(oObj)      ' Returns "Selection"
  Debug.Print oObj.Type           ' Returns 3 = ppSelectionText
  Debug.Print oObj.TextRange.text ' Error "The specified value is out of range."
End Sub

Open in new window

NorieAnalyst Assistant

Commented:
Are you trying to do something with the chart title?
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Jamie GarrochSenior Technical Consultant at BrightCarbon

Author

Commented:
Yes, but only as an example so even though I know how to reference it via the Chart OM, this isn't the use case. I need to be able to get a reference to the user-selected text within the chart and then apply a format to that text.
NorieAnalyst Assistant

Commented:
So even if they had only selected  part of the chart title you would want a reference to that?
Jamie GarrochSenior Technical Consultant at BrightCarbon

Author

Commented:
Ideally, yes.
NorieAnalyst Assistant

Commented:
I'm not sure the selected text can really be treated as an 'object'.

Did you check out the properties of oObj in the Locals Window when running the code you posted?
Jamie GarrochSenior Technical Consultant at BrightCarbon

Author

Commented:
Yes. It returns the type as ppSelectionText but no TextRange/TextRange2 object. Interestingly, the ShapeRange object returns the Chart object and not the Chart's Title object. So it looks like I can't get a reference to the selection inside a chart object.

locals window with Chart title selected
NorieAnalyst Assistant

Commented:
Have you tried looking at the ChartTitle property of the Chart object?

If you can access that you should be able to access the Characters object of its TextFrame object.

Mind you, I don't think you'll be able to determine/access the selected characters - there's no SelStart/SelLength property as this is for a Textbox on a userform.
CEO PowerPoint Alchemy
Commented:
You cannot refer to anything selected within a slide in PowerPoint (You can in Excel) I have complained to the PPT team a zillion times!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial