Private Sub to alert (message Box) if chart data is outside min and max axis limits

[sdoppke]

hi everyone, I have a dynamic chart that is created using offsets, that has min and max axis limits to exclude outlier data. Would anyone be able to help me (using VBA) create a messge box that will alert the user if the any of the data is outside those limits? If it helps the Min and Max values are store in a cell somewhere.

Thanks a ton for any help.

sd

 

[tweedle]

hi everyone, I have a dynamic chart that is created using offsets, that has min and max axis limits to exclude outlier data. Would anyone be able to help me (using VBA) create a messge box that will alert the user if the any of the data is outside those limits? If it helps the Min and Max values are store in a cell somewhere.

Thanks a ton for any help.

sd

this may get you started

Sub Usage()
    
    'Set Chart Axis Limits - two methods shown
    SetChartAxisLimits 1, 2, 14
    SetChartAxisLimits "Chart1", 2, 14
    
    
    'Performs the test for points out of the chart
    retval = PointsOutOfBounds(1, 1)
    retval = PointsOutOfBounds("Chart1", 1)
    If retval > 0 Then MsgBox "There are " & retval & " points 'off the chart!'"
    
End Sub
Function PointsOutOfBounds(chrtIdx As Variant, SeriesIdx As Variant)
'---------------------------------------------------------------------------------------
' Procedure : PointsOutOfBounds
' Purpose   : Determines how many points are not shown on a chart
'             due to being outside the chart's Axis Min/Max
'---------------------------------------------------------------------------------------
'
    Dim aSeriesColl As SeriesCollection
    Dim aSeries As Series
    Dim pointsout As Integer    'count of points outside axis Min/Max
    
    PointsOutOfBounds = 0
    
    Set aSeriesColl = Charts(chrtIdx).SeriesCollection
    Set aSeries = Charts(chrtIdx).SeriesCollection(SeriesIdx)
   
    'Get the Min/Max from Chart
    minScale = Charts(chrtIdx).Axes(xlCategory).MinimumScale
    maxScale = Charts(chrtIdx).Axes(xlCategory).MaximumScale
    
    With aSeries
        seriesname = .Name
        For ptIdx = 1 To .Points.Count
            .Points(ptIdx).Select
            If .Values(ptIdx) < minScale Or _
            .Values(ptIdx) > maxScale Then
                pointsout = pointsout + 1
            End If
        Next ptIdx
    End With
PointsOutOfBounds = pointsout
End Function

Sub SetChartAxisLimits(ChartIdx As Variant, Min As Double, Max As Double)
'---------------------------------------------------------------------------------------
' Procedure : SetChartAxisLimits
' Purpose   : Set a Chart's Axis Min/Max values
'---------------------------------------------------------------------------------------
'
    Charts(ChartIdx).Axes(xlCategory).MinimumScale = Min
    Charts(ChartIdx).Axes(xlCategory).MaximumScale = Max
End Sub

 

[sdoppke]

this may get you started

Sub Usage()
 
    'Set Chart Axis Limits - two methods shown
    SetChartAxisLimits 1, 2, 14
    SetChartAxisLimits "Chart1", 2, 14
 
 
    'Performs the test for points out of the chart
    retval = PointsOutOfBounds(1, 1)
    retval = PointsOutOfBounds("Chart1", 1)
    If retval > 0 Then MsgBox "There are " & retval & " points 'off the chart!'"
 
End Sub
Function PointsOutOfBounds(chrtIdx As Variant, SeriesIdx As Variant)
'---------------------------------------------------------------------------------------
' Procedure : PointsOutOfBounds
' Purpose   : Determines how many points are not shown on a chart
'             due to being outside the chart's Axis Min/Max
'---------------------------------------------------------------------------------------
'
    Dim aSeriesColl As SeriesCollection
    Dim aSeries As Series
    Dim pointsout As Integer    'count of points outside axis Min/Max
 
    PointsOutOfBounds = 0
 
    Set aSeriesColl = Charts(chrtIdx).SeriesCollection
    Set aSeries = Charts(chrtIdx).SeriesCollection(SeriesIdx)
 
    'Get the Min/Max from Chart
    minScale = Charts(chrtIdx).Axes(xlCategory).MinimumScale
    maxScale = Charts(chrtIdx).Axes(xlCategory).MaximumScale
 
    With aSeries
        seriesname = .Name
        For ptIdx = 1 To .Points.Count
            .Points(ptIdx).Select
            If .Values(ptIdx) < minScale Or _
            .Values(ptIdx) > maxScale Then
                pointsout = pointsout + 1
            End If
        Next ptIdx
    End With
PointsOutOfBounds = pointsout
End Function
 
Sub SetChartAxisLimits(ChartIdx As Variant, Min As Double, Max As Double)
'---------------------------------------------------------------------------------------
' Procedure : SetChartAxisLimits
' Purpose   : Set a Chart's Axis Min/Max values
'---------------------------------------------------------------------------------------
'
    Charts(ChartIdx).Axes(xlCategory).MinimumScale = Min
    Charts(ChartIdx).Axes(xlCategory).MaximumScale = Max
End Sub

I really appreciate the reply. I am trying to test this and am getting a runtime error "Script Out of Range" are there value i need to change on the script? Im sure im just not noticing something :0

sd

 

[tweedle]

How are you calling the code?
Are your Charts sheets unto themselves or embedded in a worksheet?

 

[sdoppke]

How are you calling the code?
Are your Charts sheets unto themselves or embedded in a worksheet?

My chart is embedded into a worksheet.