Solved

Toggling Data Label Values on and off in MS Chart

Posted on 2006-11-13
9
798 Views
Last Modified: 2012-05-05
Hi,

I have a line chart that I've gone into "Chart Options","Data Labels" and Selected "Value".
It puts the value of the point on the graph.
Sometimes however the graphs starts to look cluttered and I would like to toggle it on and off.

Also, I've found a few of the VB codes to programatically change things on the graphs.
Do anyone have a complete list of commands available for MS Chart 8?

Thanks
Danny
0
Comment
Question by:quickdan
  • 5
  • 4
9 Comments
 
LVL 58

Expert Comment

by:harfang
ID: 17935383
Hello quickdan

Yes, the full list is found in Excel. As it turns out, MS Chart is a subproduct of Excel, using the same library and the same constants (notice they all start with xl...).

Whenever you need to do something through code with MS Chart, create a similar chart in Excel, start macro recording and perform the change you want. Studying the resulting code should give you all needed pointers, not to mention the F1 key you can use on every method or property.

The only difference is the datasheet object of the MS Chart object, as Excel uses a worksheet range, naturally.

Hope this helps!
(°v°)
0
 

Author Comment

by:quickdan
ID: 17936022
This is great.

Followed your advice.
Having a little trouble with the syntax.

The Excel code is:
ActiveChart.ApplyDataLabels AutoText:=True, LegendKey:=False, _
        HasLeaderLines:=False, ShowSeriesName:=False, ShowCategoryName:=False, _
        ShowValue:=True, ShowPercentage:=False, ShowBubbleSize:=False

I Tried
Me![Graph0].Object.Application.Chart.axis(2).applydatalabels.showvalue = True
and
Me![Graph0].Object.Application.Chart.applydatalabels.showvalue = True
and
Me![Graph0].Object.Application.Chart.applydatalabels showvalue = True

This syntax I know works is.
Me![GraphDan].Object.Application.Chart.axes(2).minimumscale = CDbl(Me.StatMin)

I have two line graphs, so I have two axis values, so it was .axis(2,1) and .axis(2,2) to program max and min values.

Oddly enough, while getting constant error messages, stat values have appeared on my graph. So something was close.

Once I get the gist of this, I'm gona be dangerous >-)

Eagerly awaiting enlightenment.

Thanks
Danny



0
 

Author Comment

by:quickdan
ID: 17936040
Intestingly
Me![Graph0].Object.Application.Chart.applydatalabels showvalue = False
Turns off the values with no errors but the same as above with True gives:
Run-time error '1004'
ApplyDataLabels method of Chart class failed.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:quickdan
ID: 17936133
The Excel chart is not quite the same as the one I'm using in Access as there's no Secondary Axis in my Excel - all 2003
0
 
LVL 58

Expert Comment

by:harfang
ID: 17938763
quickdan,

Not much time right now. I would like to show you how to add the MS-Chart library and a chart variable to make your coding easier, that will be in a next post.

For now, Me.Graph0.Object should be a chart objet (you use Me.Graph0.Object.Application.Chart). This object can expose chart methods and properties. You want to use the ApplyDataLabels method, which uses parameters. Excel records those parameters, or arguments, as so called named arguments.

Thus, try:

    Me![Graph0].Object.Application.Chart.ApplyDataLabels ShowValue:=True

(I didn't test it) Back to you later.

(°v°)
0
 

Author Comment

by:quickdan
ID: 17941106
Thanks for looking at this.

The above syntax gives:
Run-time error '1004':
Application-defined or object-defined error

I'll hang on until you get some time.

Thanks again,
Danny
0
 
LVL 58

Accepted Solution

by:
harfang earned 250 total points
ID: 17942972
quickdan,

I made the test. This seems to work, but for MS Graph 10.0.


Private Sub cmdTest_Click()
   
    Dim objChart As Graph.Chart
   
    Set objChart = Graph0.Object
   
    ' turn on:
    objChart.ApplyDataLabels Type:=xlDataLabelsShowValue
   
    ' turn off:
    objChart.ApplyDataLabels Type:=xlDataLabelsShowNone
   
End Sub


This will work only with the appropriate library loaded: from VB, Tools / References, check "Microsoft Graph ?.? Object Library". The advantages are that you can create objects of the correct type (so called "early binding"), and that you can use all the xl??? constants.

See if this works for you. Good luck!

(°v°)
0
 

Author Comment

by:quickdan
ID: 17944390
Great.
Super.
Lovely.
Smashing.

Give a man some code and he'll ask for some more.
Give a man a object library and ....... you've ruined his eating and sleeping habits for a week.

Arrrrr, Master!

Tar very much, perfect.

Danny

(I'm using Microsoft Graph 11 - code works great though. I have more messing to do with the font size and color but it should be no problem from here on out.)

Thanks again.
0
 
LVL 58

Expert Comment

by:harfang
ID: 17944531
*LOL* Your enthousiasm is refreshing! Happy hacking!
(°v°)
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
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…

749 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