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

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?
LVL 15
Jamie GarrochSenior Technical Consultant at BrightCarbonAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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 BrightCarbonAuthor 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?
Price Your IT Services for Profit

Managed service contracts are great - when they're making you money. Yes, you’re getting paid monthly, but is it actually profitable? Learn to calculate your hourly overhead burden so you can master your IT services pricing strategy.

Jamie GarrochSenior Technical Consultant at BrightCarbonAuthor 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 BrightCarbonAuthor 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 BrightCarbonAuthor 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.
John WilsonCEO PowerPoint AlchemyCommented:
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!

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VBA

From novice to tech pro — start learning today.