Toggling Data Label Values on and off in MS Chart

Posted on 2006-11-13
Last Modified: 2012-05-05

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?

Question by:quickdan
  • 5
  • 4
LVL 58

Expert Comment

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!

Author Comment

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
Me![Graph0].Object.Application.Chart.applydatalabels.showvalue = True
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.



Author Comment

ID: 17936040
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.
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.


Author Comment

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
LVL 58

Expert Comment

ID: 17938763

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.


Author Comment

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,
LVL 58

Accepted Solution

harfang earned 250 total points
ID: 17942972

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!


Author Comment

ID: 17944390

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.


(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.
LVL 58

Expert Comment

ID: 17944531
*LOL* Your enthousiasm is refreshing! Happy hacking!

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

825 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