Solved

Toggling Data Label Values on and off in MS Chart

Posted on 2006-11-13
9
758 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 

Author Comment

by:quickdan
Comment Utility
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
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 
LVL 58

Expert Comment

by:harfang
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
*LOL* Your enthousiasm is refreshing! Happy hacking!
(°v°)
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

772 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

16 Experts available now in Live!

Get 1:1 Help Now