Urgent - Help Needed for PowerPoint Macro - Multiple If condition

[anappleuser]

Hi I am developing a macro which Removes less than 5.00% data from charts. (Just removes from Charts, Not from inside datasheet).

I got problem in Cluster Bar chart type in which I want to Apply two condition. If Chart type is 57 (XlBarCluster) and also Has Axis then Do not show data labels on chart Else Show data labels on Chart. Below is my code but it is giving error on Highlighted Part. Please Help.

Sub RemoveSmallValuesFromExecutiveReportCharts()
    For Each Slide In ActivePresentation.Slides
      For Each Shape In Slide.Shapes
       If Shape.HasChart Then
          Set Chart = Shape.Chart
            If Chart.ChartType = 53 Or Chart.ChartType = 59 Or Chart.ChartType = 57 Or Chart.ChartType = 52 Or Chart.ChartType = 58 Or Chart.ChartType = 5 Or Chart.ChartType = -4120 Then
            RemoveSmallValues Chart
               'For Each Series In Chart.SeriesCollection
                If Chart.ChartType = 57 And Chart.HasAxis(xlCategory) Then
                 [B]Series.HasDataLabels = False[/B]
            End If
          End If
        End If
      Next Shape
    Next Slide
 End Sub

--------------------------------------

Sub RemoveSmallValues(Chart)
    
    Const valueThreshold As Double = 0.05
    
    For Each Series In Chart.SeriesCollection
   '   Series.HasDataLabels = False
      Series.HasDataLabels = True
  
        Dim pointCount As Integer
        Dim pointValues As Variant
  
        pointCount = Series.Points.Count
        pointValues = Series.Values
  
        For pointIndex = 1 To pointCount
          If pointValues(pointIndex) < valueThreshold Then
            Series.Points(pointIndex).HasDataLabel = False
            Else
            Series.Points(pointIndex).HasDataLabel = True
          End If
        Next pointIndex
    Next Series
End Sub

 

[Domenic]

Does this do what you want?

[color=darkblue]Sub[/color] RemoveSmallValuesFromExecutiveReportCharts()
    [color=darkblue]For[/color] [color=darkblue]Each[/color] Slide [color=darkblue]In[/color] ActivePresentation.Slides
        [color=darkblue]For[/color] [color=darkblue]Each[/color] Shape [color=darkblue]In[/color] Slide.Shapes
            [color=darkblue]If[/color] Shape.HasChart [color=darkblue]Then[/color]
                [color=darkblue]Set[/color] Chart = Shape.Chart
                [color=darkblue]If[/color] Chart.ChartType = 53 [color=darkblue]Or[/color] Chart.ChartType = 59 [color=darkblue]Or[/color] Chart.ChartType = 57 [color=darkblue]Or[/color] Chart.ChartType = 52 [color=darkblue]Or[/color] Chart.ChartType = 58 [color=darkblue]Or[/color] Chart.ChartType = 5 [color=darkblue]Or[/color] Chart.ChartType = -4120 [color=darkblue]Then[/color]
                    RemoveSmallValues Chart
                    [color=darkblue]If[/color] Chart.ChartType = 57 [color=darkblue]Then[/color]
                        [color=darkblue]For[/color] [color=darkblue]Each[/color] Series [color=darkblue]In[/color] Chart.SeriesCollection
                            Series.HasDataLabels = [color=darkblue]Not[/color] Chart.HasAxis(xlCategory)
                        [color=darkblue]Next[/color] Series
                    [color=darkblue]End[/color] [color=darkblue]If[/color]
                [color=darkblue]End[/color] [color=darkblue]If[/color]
            [color=darkblue]End[/color] [color=darkblue]If[/color]
        [color=darkblue]Next[/color] Shape
    [color=darkblue]Next[/color] Slide
 [color=darkblue]End[/color] [color=darkblue]Sub[/color]

 

[anappleuser]

Hi Domenic,

Thank you for your reply. Your code removes data labels from all Clustered Bar Charts. I want to apply two condition in code.
1. If Cluster Bar chart has X Axis displayed on chart then do not show data labels.
2. If Cluster Bar chart do not have X Axis on chart then display data labels.

Can we modify our code accordingly ? Thanks for your efforts.

 

[Domenic]

In that case, try the following instead...

Sub RemoveSmallValuesFromExecutiveReportCharts()
    For Each Slide In ActivePresentation.Slides
        For Each Shape In Slide.Shapes
            If Shape.HasChart Then
                Set Chart = Shape.Chart
                If Chart.ChartType = 53 Or Chart.ChartType = 59 Or Chart.ChartType = 57 Or Chart.ChartType = 52 Or Chart.ChartType = 58 Or Chart.ChartType = 5 Or Chart.ChartType = -4120 Then
                    RemoveSmallValues Chart
                   [COLOR=#ff0000] If Chart.ChartType = 57 Then
                        For Each Series In Chart.SeriesCollection
                            Series.HasDataLabels = Not Chart.HasAxis(xlCategory)
                        Next Series
                    End If[/COLOR]
                End If
            End If
        Next Shape
    Next Slide
 End Sub

Hope this helps!

 

[anappleuser]

Hi Domenic,

Sorry to say but it is still not getting through. might be my explanation of my requirement was not proper.

I am trying to explain it again. My main task is to remove less than 5.00% data from all charts. But specifically for Cluster Bar chart ( code number 57) I want to check two conditions.

1). If Cluster Bar Chart has X Axis, then do nothing. Do not show any single data label.
but
2). If Cluster Bar chart do not have X Axis then Display data labels. (removing less than 5 % data labels).

Hope I am clear this time

Thank you very much for your reply and patience

 

[Domenic]

Oh I see. In that case, your original code can be modified as follows...

Sub RemoveSmallValuesFromExecutiveReportCharts()
    For Each Slide In ActivePresentation.Slides
      For Each Shape In Slide.Shapes
       If Shape.HasChart Then
          Set Chart = Shape.Chart
            If Chart.ChartType = 53 Or Chart.ChartType = 59 Or Chart.ChartType = 57 Or Chart.ChartType = 52 Or Chart.ChartType = 58 Or Chart.ChartType = 5 Or Chart.ChartType = -4120 Then
                RemoveSmallValues Chart
                [COLOR=#ff0000]If Chart.ChartType = 57 And Chart.HasAxis(xlCategory) Then
                    For Each Series In Chart.SeriesCollection
                        Series.HasDataLabels = False
                    Next Series
                End If[/COLOR]
            End If
          End If
        End If
      Next Shape
    Next Slide
 End Sub

Does this help?

 

[anappleuser]

Hi Domenic,

It is still removing all data labels from all Cluster Bar charts. It Should exclude Cluster Bar Charts with X axis.

Should we change the place of Code ?

 

[Domenic]

The code should do the following...

1) For each of the specified chart type, including clustered bar charts, it will display the data label for any point whose value is greater than or equal to 5%, and remove those whose value is less than 5%.

2) For clustered bar charts, if the X axis is displayed, it removes any and all data labels. So there would be no data labels showing at all. However, if the X axis isn't displayed, it does nothing. So a data label would already be showing for any point whose value is greater than or equal to 5%, and a data label would have already been removed for those less than 5%.

Is this what you want?

If not, can you please clarify?

Thanks!

P.S. Your image isn't showing.

 

[anappleuser]

Hi Domenic,

Sorry for late reply as I was sick.

Yes, you are right at your point one.

For point two, If the X Axis isn't displayed, than Show the Data labels as per <5.00% rule.

we need to modify our macro in that way.

Thanks

 

[anappleuser]

And i was unable to insert the screenshot.