CODEWITHSUNDEEP
excelvba
XtremeBaumer
XtremeBaumer

Reputation: 6435

Excel VBA check chart type on SeriesCollection

I need to re-scale the Y-axis of my charts in an excel sheet. The scale should be adjusted by the values inside each chart.

I found following code online:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cht As ChartObject
Dim srs As Series
Dim FirstTime  As Boolean
Dim MaxNumber As Double
Dim MinNumber As Double
Dim MaxChartNumber As Long
Dim MinChartNumber As Long
Dim Padding As Double

'Input Padding on Top of Min/Max Numbers (Percentage)
  Padding = 5  'Number between 0-1

'Optimize Code
  Application.ScreenUpdating = False
  
'Loop Through Each Chart On ActiveSheet
  For Each cht In ActiveSheet.ChartObjects
    
    'First Time Looking at This Chart?
      FirstTime = True
      
    'Determine Chart's Overall Max/Min From Connected Data Source
      For Each srs In cht.Chart.SeriesCollection
        'Determine Maximum value in Series
          MaxNumber = Application.WorksheetFunction.max(srs.Values)
        
        'Store value if currently the overall Maximum Value
          If FirstTime = True Then
            MaxChartNumber = MaxNumber
          ElseIf MaxNumber > MaxChartNumber Then
            MaxChartNumber = MaxNumber
          End If
        
        'Determine Minimum value in Series (exclude zeroes)
          MinNumber = Application.WorksheetFunction.min(srs.Values)
          
        'Store value if currently the overall Minimum Value
          If FirstTime = True Then
            MinChartNumber = MinNumber
          ElseIf MinNumber < MinChartNumber Or MinChartNumber = 0 Then
            MinChartNumber = MinNumber
          End If
        
        'First Time Looking at This Chart?
          FirstTime = False
      Next srs
      
    'Rescale Y-Axis
      cht.Chart.Axes(xlValue).MinimumScale = MinChartNumber - Padding + 1
      cht.Chart.Axes(xlValue).MaximumScale = MaxChartNumber + Padding + 1
  
  Next cht

'Optimize Code
  Application.ScreenUpdating = True
End Sub

It is doing what I want. Now I just need the code to only take the Chart.SeriesCollection line series.

At first I thought about checking the chart type to equal a line chart (wouldn't know how to do this), but that would probably ignore combo charts. I have a combo chart in my excel which consists of 3x "100% Stacked Column" series (these are marked with secondary axis) and 2x "Line" series. In this case I would only need to look at the two line series.

Does Chart.SeriesCollection.ChartType return the respective type as outlined above? And if so, how can I check for "Line" only?

Upvotes: 1

Views: 1222

Answers (1)

Jon Peltier
Jon Peltier

Reputation: 6053

So you need something like this:

For Each srs In cht.Chart.SeriesCollection
    If srs.ChartType = xlLine Then

        ' do your stuff

    End If
Next

Upvotes: 1

Related Questions