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.

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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

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

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

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

932 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

8 Experts available now in Live!

Get 1:1 Help Now