Creating charts from selected data

[redbos]

Hello all,
So I created the following macro to act as an easy way to take user selected data, create a chart and apply a specific template. The reasoning is that we need standardized chart templates deployed across our company. This code will be added to the Ribbon as a macro, and used by everyone. I guess what I'm looking for is if anyone knows of more efficient ways to do this? Are there ways to directly edit the Ribbon in Excel 2010? Thanks for any feedback.

Sub Create_Scatter_GenericNumbers()
'http://www.databison.com/index.php/vba-chart/
'this works!!!!

    Dim ChartObj As ChartObject
    Dim DataRange As Range
 
    Set ChartObj = ActiveSheet.ChartObjects.Add(Left:=100, Width:=650, Top:=75, Height:=400)
    Set DataRange = Selection

   With ChartObj
        
        .Chart.SetSourceData Source:=Selection
        .Chart.ChartType = xlXYScatter
        .Chart.ApplyChartTemplate ("P:\Scatter_GenericNumbers.crtx")
    
    End With
End Sub

 

[redbos]

okay, so I've made an upgrade to my code, as seen below. It now uses an IF/ELSE statement to see if data or a graph is selected, then creates a graph or applies the template. However, I'm getting an error if I try to switch between graph types.

The question I now have is:

How can I change the chart type and then apply the template (in the 2nd part of the code)? Is there an ActiveChart command that will alter the currently selected chart so that my template can be successfully applied?

Sub InsertFormat_PieGraph()

    Dim ChartObj As ChartObject
    Dim DataRange As Range

' if a chart is not selected, we assume that data is selected and create a graph

        If ActiveChart Is Nothing Then
                Set DataRange = Selection
                Set ChartObj = ActiveSheet.ChartObjects.Add(Left:=100, Width:=500, Top:=75, Height:=350)
    
                With ChartObj
                    .Chart.SetSourceData Source:=Selection
                    .Chart.ChartType = xlPie
                    .Chart.ApplyChartTemplate ("P:\Group\Engineering Resources\Excel 2010 Chart Templates\PieChart.crtx")
                End With
        Else

'if chart is selected, then it will format using templates
'http://www.databison.com/index.php/vba-chart/
     
            ActiveChart.ApplyChartTemplate ( _
            "P:\Group\Engineering Resources\Excel 2010 Chart Templates\PieChart.crtx" _
            )
            
        End If

End Sub

 

[redbos]

here is my change, if anyone is interested...

ActiveChart.ChartType = xlPie
            ActiveChart.ApplyChartTemplate ( _
            "P:\Group\Engineering Resources\Excel 2010 Chart Templates\PieChart.crtx" _
            )
            
        End If

End Sub