Solved

Check for the existence of data labels

Posted on 2011-03-06
8
522 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 14

Expert Comment

by:Zack Barresse
ID: 35046969
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 42

Accepted Solution

by:
dlmille earned 400 total points
ID: 35048056
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
ID: 35048067
@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
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!

 
LVL 42

Expert Comment

by:dlmille
ID: 35048091
@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
 
LVL 14

Expert Comment

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

Author Comment

by:AndreasHermle
ID: 35048300
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
ID: 35055123
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
ID: 35058456
Dear all,

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

Regards, Andreas
0

Featured Post

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

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

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

696 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