Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Toggling Data Label Values on and off in MS Chart

Posted on 2006-11-13
9
Medium Priority
?
829 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 1000 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

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

571 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