PDA

View Full Version : Solved: Chart Macro



rodneywead
04-22-2011, 08:10 AM
I am using Excel 2010. I am trying to set up a chart macro that I can run once I make a pivot table. I recorded the macro and this is the code I got.

Sub Chart()
'
' Chart Macro
'
' Keyboard Shortcut: Ctrl+q
'
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlColumnClustered
ActiveChart.SetSourceData Source:=Range("'Root Cause'!$A$3:$B$17")
End Sub

When I try and run it on other charts it gives me an error message if the chart range is not $A$3:$B$17. How do I set this up so I can run it no matter what the range of the chart is?

Thanks,


Rodney

Bob Phillips
04-22-2011, 08:19 AM
Sub Chart()
Dim rng As Range
Dim chartObj As ChartObject

On Error Resume Next
Set rng = Application.InputBox("Use the mouse to select the data to chart", Type:=8)
On Error GoTo 0
If Not rng Is Nothing Then

Set chartObj = ActiveSheet.ChartObjects.Add(100, 30, 400, 250)

With chartObj.Chart

.ChartType = xlColumnClustered
.SetSourceData Source:=rng
End With
End If
End Sub

rodneywead
04-22-2011, 08:34 AM
That worked. Thanks.

:hi: