Toggle Between Chart Types

[excel_desperate]

I want to toggle between chart types on my dashborad charts, like say I already have a bar graph depicted on the worksheet and if a user clicks on the graph the same graph with the same data gets changed to a line graph or xy scatter and it keeps inter-changing.

 

[Hui]

Mr Excel Desperate

Try pasting the following code into a Module

[pre]

Sub Click_cht()
'
' Click Chart to scroll through several chart types
'
' posted on Chandoo.org
' By Hui...
'

On Error Resume Next

ActiveSheet.ChartObjects("Chart 1").Activate 'Chart name
' Change the Charts Name to match your Charts name

[H1] = [H1] + 1
If [H1] = 6 Then [H1] = 1

Select Case [H1]
Case 1
ActiveChart.ChartType = xlLine
Case 2
ActiveChart.ChartType = xlPie
Case 3
ActiveChart.ChartType = xlBarClustered
Case 4
ActiveChart.ChartType = xlArea
Case 5
ActiveChart.ChartType = xlXYScatterSmooth
End Select

[H2].Select

End Sub

[/pre]

Now Right Click on the Chart and Select Assign Macro

Select Click_cht and Ok

 

[excel_desperate]

Hi Hui,


I tried doing it, but I am sorry it doesn't works. It would require chart events to be tracked, but I am not familiar with the use of it. Hope someone knows about it and he/she would help me explain and make me acheive doing it.


Thanks.

 

[Hui]

Copy the code

Alt F11

Click on the Page of your spreadsheet (in left hand pane)

paste the code into the right hand pane


Save your spreadsheet as a Macro Enabled Worksheet

Re-open and enjoy


Check that your chart is Called Chart1, adjust macro if not

 

[Clarity]

Great piece of functionality Hui.


I beleive to make it work you need to right click the chart and assign the macro to the chart.


I have posted a simple example here:


http://cid-4702efbc51549018.office.live.com/view.aspx/Chandoo%20Forum/Toggle%20Chart.xlsm

 

[Hui]

Thanx Clarity, You are correct and I have amended my post.


It must have been a late night...