Solved

How to reference custom text box on graph using VBA

Posted on 2011-02-21
13
970 Views
Last Modified: 2012-05-11
What is the syntax for referencing a 'custom' text box using MS Grpah.
I'm using Access. My form is [frmChart3] my chart is [oleChart1]
I know how to reference the chart title:

Forms!frmChart3!oleChart1.charttitle.text
or
Forms!frmChart3!oleChart1.Object.Application.Chart.Charttitle.text

But you can also just add addtional text boxes to the chart by just starting to type on the chart object. I created two of them. In the attached snapshot, I clicked on one of them and the name "text box 2" pops up. So how to I reference that box's contents in VBA?
 Custom Chart TExt Box
0
Comment
Question by:askolits
  • 6
  • 6
13 Comments
 
LVL 15

Expert Comment

by:Simon Ball
Comment Utility
does the chart object have a controls collection?

i've gone through and looked and i think its part of the object which is not exposed, so you cannot amend it or read from it via code.....

For Each prp In Forms!frmChart3!olechart1.Properties

MsgBox prp.Name

Next

is didn't pop up in the list...

you can put an ordinary textbox on your form and place it over the chart control and treat that as normal:

Text2.Value = "change my display"

or you could use a label.caption instead.
 Database5.mdb
0
 

Author Comment

by:askolits
Comment Utility
I really want to have the text box within the chart rather than a seperate object on the form.
Don't see any controls collection when I look at the Graph object browser.

I'll keep searching to find out if it's possible exposed or not
0
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 50 total points
Comment Utility
I tried this every way I knew how.
Including refencing MS Graph Object Library and doing things like this:

'Dim chrt As Object
'Set chrt = Me.Graph0
'MsgBox chrt![Text Box 1].text

'Dim chrt As Object
'Set chrt = Me.Graph0
'MsgBox chrt![Text Box 1].Caption


'Dim chrt As Chart
'Set chrt = Me.Graph0
'MsgBox chrt![Text Box 1].text

'Dim chrt As Chart
'Set chrt = Me.Graph0
'MsgBox chrt![Text Box 1].Caption

'Dim chrt As Graph
'Set chrt = Me.Graph0
'MsgBox chrt![Text Box 1].text

'Dim chrt As Graph
'Set chrt = Me.Graph0
'MsgBox chrt![Text Box 1].Caption

'Dim chrt As Object
'Set chrt = Reports![Report1]!Graph0
'MsgBox chrt![Text Box 1].Caption

'Dim chrt As Graph
'Set chrt = Reports![Report1]!Graph0
'MsgBox chrt![Text Box 1].Caption

'Dim chrt As Chart
'Set chrt = Reports![Report1]!Graph0
'MsgBox chrt![Text Box 1].Caption

I even tried opening the Report in Design view:
DoCmd.OpenReport "Report1", acViewDesign, , , acHidden
Reports!Report1!Graph0![Text Box 1] = "Billy"
DoCmd.Close , , acSaveYes


...et all
But none seem to work.

Can I ask why this textbox must be *in* the chart object itself?

The syntax to set the value of a simple textbox on the Report/Form, itself, ...would be incredibly simple:
Me.TextBox1="Some Text"

So why even bother with trying to reference an internal textbox.

Besides it states here: (http://www.eggheadcafe.com/software/aspnet/32436286/modify-a-text-box-into-a-chart.aspx) that this may not even be possible.

Again, why bother...?

The only thing you would have to manage (if you just used a simple textbox on the Form/Report) is making sure that the textbox is "In front of" the Graph object. (which is simple)

JeffCoachman
0
 

Author Comment

by:askolits
Comment Utility
There are a bunch of ways  to do it without having to use an embedded text box. It's just much cleaner that way. The same graph may be used for various data and having the ability to have specific info for each graph on the screen is helpful. Copying the object to multiple forms and reports also makes things a lot easier. No need to recreate extra text boxes.  Sure, I could use non-embedded text boxes, but even when you set  the object’s order (front or back), you can't be guaranteed it will display correctly (been there, done that). It’s really no big deal. I’ll just modify the ChartTitle property as needed.
So, is it possible to change the embedded chart title? Absolutely. Been doing it in various apps for about 10 years.
Here’s how:  Forms!frmChart3!oleChart1.charttitle.text = “This is the new title”
I reviewed that article you referenced and her response was a bit misleading. Not sure if you can modify the shape of the text box or other characteristics, but you can certainly modify its contents. Since the person asking the question wanted to do exactly that, her answer was essentially incorrect.

John Skolits
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
"is it possible to change the embedded chart title? Absolutely...."
I don't believe this was ever the question (The question was editing the text of a textbox embedded in a Chart/Graph)..., besides, I knew this already...

<Not sure if you can modify the shape of the text box or other characteristics, but you can certainly modify its contents.>
Again, ...I know that this can be done with the Chart Title, but the Chart Title object is not technically a "TextBox"
(You can only have one ChartTitle Object per chart/Graph, ...you can have as many textboxes as you like.)
Again, if the ChartTitle Object was the same thing as a Textbox Object, then you would be able to edit the TextBox text using the same VBA syntax you posted for the Chart Title, ...but yet you cannot....
Make sense?

Note the attached screenshot and notice how they highlight differently when selected.
Among the differences, Note that the textbox has resizing handles (Small circles) when selected, while the ChartTitle does not...
(Also note that when you select each chart object, it's name appears in the name box on the Toolbar.)

<Since the person asking the question wanted to do exactly that, her answer was essentially incorrect.>
Well, until you can show that you can edit the text of a textbox embedded into a Chart/Ghaph, then the answer IS correct.

(Not that it matters here, but I know Cindy Meister, the person who made the statement, as a fellow MVP)


JeffCoachman
untitled1.JPG
untitled2.JPG
0
 

Author Comment

by:askolits
Comment Utility
Oh, OK. I wasn't carefull enough in the way I read the other question. You are correct and I am wrong on that one. She was correct in her response. Sorry.
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
No problem.
0
 

Author Closing Comment

by:askolits
Comment Utility
The bottom line. No way to access the custom text boxes within MSGraph chart object.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
Follow up.

I will double check this in Acc 2010.
0
 

Author Comment

by:askolits
Comment Utility
OK thanks.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
OK, I tried this in Access 2010 and it still does not work.
:-(
0
 

Author Comment

by:askolits
Comment Utility
Thanks for trying. The only documantation I've been able to find was on MSGraph version 8.
And it was no help for the version I currently have, MSGraph 12
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
Yeah,

Sad, because there should be a way to do this...
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

My experience with Windows 10 over a one year period and suggestions for smooth operation
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now