We help IT Professionals succeed at work.

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

180 Views
Last Modified: 2019-01-26
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

NorieAnalyst Assistant
CERTIFIED EXPERT

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 Garroch (MVP)PowerPoint Technical Consultant
CERTIFIED EXPERT

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
CERTIFIED EXPERT

Commented:
Are you trying to do something with the chart title?
Jamie Garroch (MVP)PowerPoint Technical Consultant
CERTIFIED EXPERT

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
CERTIFIED EXPERT

Commented:
So even if they had only selected  part of the chart title you would want a reference to that?
Jamie Garroch (MVP)PowerPoint Technical Consultant
CERTIFIED EXPERT

Author

Commented:
Ideally, yes.
NorieAnalyst Assistant
CERTIFIED EXPERT

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 Garroch (MVP)PowerPoint Technical Consultant
CERTIFIED EXPERT

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
CERTIFIED EXPERT

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
CERTIFIED EXPERT
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions