Solved

Check for the existence of data labels

Posted on 2011-03-06
8
521 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
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
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

789 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