cellMatrix.net

VBA Function to Screen for Certain Charts

Until late last year I've primarily worked with Excel 2003 because that's what we use at work. However, that changed over the holidays because my home computer crashed and the new one came with Excel 2007. I was trying to wait until Excel 2010 was available before buying a new computer but the old machine just couldn't make it that long.

It seems like most of what I've read regarding the Excel 2007 ribbon has been negative. However, after using Excel 2007 for a couple of weeks my initial impressions are that I like it. Although the ribbon can't be modified as easy as the toolbars were in previous versions, having the ability to create your own ribbon tabs makes up for that problem for me. And having the ability to customize the quick access toolbar makes up for inefficiencies in the placement of the ribbon commends.

Now that I've made the move to 2007, I need to rebuild several add-ins that I've created to interface with the ribbon. One of the add-ins that I've created is for working with embedded charts only. Built into that add-in is a function written by John Walkenbach that alerts the user if he or she failed to select a chart. After screening for a chart selection, I've added a second chart-screening function that utilizes a case statement to alert the user if an "improper" chart type is selected. In the example, an improper chart is by my definition only. That second chart-screening function follows:

First, declare an optional public variable containing the name of the application (add-in).

Option Explicit
Public Const APPNAME As String = "Position Chart Labels"

Next, provide the code that triggers the user form. "ValidContext" references the chart screening function below.

Sub MoveChartLabels()
    If ValidContext(True) Then frmMoveLabels.Show
End Sub

Finally, provide the chart screening function. If the chart type appears as a line in the code below the function considers the chart to be valid. If not, the function kicks out an error message. The function works with combination charts because it evaluates each individual chart series for the accepted chart type.

Private Function ValidContext(ChtType) As Boolean
   
    Dim Sr As Series
    Dim Srs As SeriesCollection
    
    Const MsgChtType As String = "Invalid chart type."
        
    Set Srs = ActiveChart.SeriesCollection
    
    For Each Sr In Srs
     
        Select Case Sr.ChartType
            
            'Accepted Line Charts
            Case xlLine: ValidContext = True
            Case xlLineStacked: ValidContext = True
            Case xlLineStacked100: ValidContext = True
            Case xlLineMarkers: ValidContext = True
            Case xlLineMarkersStacked: ValidContext = True
            Case xlLineMarkersStacked100: ValidContext = True
            
            'Accepted Column Charts
            Case xlColumnClustered: ValidContext = True
            Case xlColumnStacked: ValidContext = True
            Case xlColumnStacked100: ValidContext = True
        
            'Accepted Bar Charts
            Case xlBarClustered: ValidContext = True
            Case xlBarStacked: ValidContext = True
            Case xlBarStacked100: ValidContext = True
                    
            'Accepted Area Charts
            Case xlArea: ValidContext = True
            Case xlAreaStacked: ValidContext = True
            Case xlAreaStacked100: ValidContext = True
            
            'Accepted XY Scatter Charts
            Case xlXYScatter: ValidContext = True
            Case xlXYScatterSmooth: ValidContext = True
            Case xlXYScatterSmoothNoMarkers: ValidContext = True
            Case xlXYScatterLines: ValidContext = True
            Case xlXYScatterLinesNoMarkers: ValidContext = True
    
            'Accepted Pie Charts
            Case xlPie: ValidContext = True
            Case xlPieExploded: ValidContext = True
            Case xlPieOfPie: ValidContext = True
            Case xlBarOfPie: ValidContext = True
            
            Case Else
                ValidContext = False
                MsgBox MsgChtType, vbCritical, APPNAME
                Exit Function
        
        End Select

    Next Sr
    
End Function