Avatar of Jamie Garroch (MVP)
Jamie Garroch (MVP)
Flag for United Kingdom of Great Britain and Northern Ireland asked on

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?
VBAMicrosoft PowerPointMicrosoft ExcelMicrosoft Office

Avatar of undefined
Last Comment
John Wilson

8/22/2022 - Mon
Norie

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)

ASKER
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

Norie

Are you trying to do something with the chart title?
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Jamie Garroch (MVP)

ASKER
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.
Norie

So even if they had only selected  part of the chart title you would want a reference to that?
Jamie Garroch (MVP)

ASKER
Ideally, yes.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Norie

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)

ASKER
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
Norie

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.
Your help has saved me hundreds of hours of internet surfing.
fblack61
ASKER CERTIFIED SOLUTION
John Wilson

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question