Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Check for the existence of data labels

Posted on 2011-03-06
8
Medium Priority
?
530 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 1600 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

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

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

604 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