Solved

Check for the existence of data labels

Posted on 2011-03-06
8
518 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 41

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

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
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.

 
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

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

Suggested Solutions

Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

910 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

23 Experts available now in Live!

Get 1:1 Help Now