Solved

How to reference custom text box on graph using VBA

Posted on 2011-02-21
13
974 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
ID: 34943013
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
ID: 34944033
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
ID: 34963840
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
ID: 34964388
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
ID: 34967766
"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
ID: 34969133
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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 34974271
No problem.
0
 

Author Closing Comment

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

Expert Comment

by:Jeffrey Coachman
ID: 34974608
Follow up.

I will double check this in Acc 2010.
0
 

Author Comment

by:askolits
ID: 34974681
OK thanks.
0
 
LVL 74

Expert Comment

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

Author Comment

by:askolits
ID: 34980527
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
ID: 34982686
Yeah,

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

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will show you how to use shortcut menus in the Access run-time environment.
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

895 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

15 Experts available now in Live!

Get 1:1 Help Now