Solved

Check for the existence of data labels

Posted on 2011-03-06
8
514 Views
Last Modified: 2012-05-11
Dear Experts:

below macro ...
... deletes any data labels from all data series of all charts on the active worksheet.

I wonder whether the macro could check at the beginning ...
... for the existence of any data labels (1 to n) and if there are none at all come up with a msgbox telling the user ('All data labels have already been deleted!').

Help is much appreciated.Thank you very much in advance.

Regards, Andreas
Sub DataLabels_Remove()
   
     Dim ChtObj As ChartObject
     Dim i As Integer
     Dim ser As Series
     i = 0

For Each ChtObj In ActiveSheet.ChartObjects
    With ChtObj.Chart
        For Each ser In .SeriesCollection
               ser.ApplyDataLabels (xlDataLabelsShowNone)
        Next ser
    End With
Next
   
End Sub

Open in new window

0
Comment
Question by:AndreasHermle
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 14

Expert Comment

by:Zack Barresse
Comment Utility
Hello,

I don't think you can check for their existence unless you loop through all the series, since they're independent.  But you can take them all away without your inner loop...

    For Each ChtObj In ActiveSheet.ChartObjects
        ChtObj.Chart.ApplyDataLabels (xlDataLabelsShowNone)
    Next

Open in new window

0
 
LVL 41

Accepted Solution

by:
dlmille earned 400 total points
Comment Utility
firefytr is correct as it seems you have to loop through the series to check each one.  I do the same (turn all off, then add back) when I work datalabels.

However, the exact answer to your question is YES - there is a HasDataLabels property:

The attached code will printout to the immediate window each chart that has or doesn't have datalabels by series.

 
Sub CheckDataLabels()
   
     Dim ChtObj As ChartObject
     Dim i As Integer
     Dim ser As Series
     Dim datalabelFlg As Boolean
     i = 0

For Each ChtObj In ActiveSheet.ChartObjects
    With ChtObj.Chart
        For Each ser In .SeriesCollection
            If ser.HasDataLabels Then
                Debug.Print "Chart " & .Name & " series " & ser.Name & " already has datalabels"
            Else
                Debug.Print "Chart " & .Name & " series " & ser.Name & " does not have datalabels"
            End If
        Next ser
    End With
Next

End Sub

Open in new window


Dave
0
 
LVL 14

Expert Comment

by:Zack Barresse
Comment Utility
@Dave: Good point.  I was assuming by "in the beginning" as in "prior to the series loop".  I guess it depends on the interpretation of the OP's question in regards to an accurate answer.  Hence the one-liner I posted to avoid the series looping.  :)
0
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
@firefytr - I prefer your approach, if and only when I am indifferent to dropping existing data labels.  If, however, the user has worked on the datalabels (re: font, colors, etc.) then knowing if they exist or not might be a desire.

If there was a way to SUM the array .seriescollection() and property .hasdatalabels then you could do that in one step as well.

I couldn't figure out how to do that.  I was trying

application.sum(application.index(.seriescollection.haslabels),0,2) but that won't work.  unless you can sum a single property in that array of .seriescollection, you still have to loop - or just drop all data labels as your solution suggested.

Dave
0
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

 
LVL 14

Expert Comment

by:Zack Barresse
Comment Utility
Sure.  I was talking purely in the context of the OP's question.  
0
 

Author Comment

by:AndreasHermle
Comment Utility
Dear firefytr, dlmille:

thank you very much for your overwhelming support. I will test both codes and get back to you with a feedback tomorrow.

Thank you very much again, Andreas
0
 
LVL 85

Assisted Solution

by:Rory Archibald
Rory Archibald earned 100 total points
Comment Utility
FYI, HasDataLabels is not reliable in 2007, in my experience (I haven't tested in 2010), as it will return False if one datalabel has been added to a point, rather than to the entire series.
0
 

Author Closing Comment

by:AndreasHermle
Comment Utility
Dear all,

thank you very much for your great and professional help. I really appreciate it.

Regards, Andreas
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

Suggested Solutions

Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

762 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

13 Experts available now in Live!

Get 1:1 Help Now