Thread: Selecting Different Chart types

Hi,

I am trying to figure out a way to switch between different chart types - eg column, bar, line, etc. I have decided that the easiest way is to create all these charts and stack them on top of each other. Next I need a macro that will allow me to select the chart type and it will bring that chart to the top of the pile. At the moment I have created Buttons and assigned a macro to each button for the different chart.

So the first button has this macro assigned to it:

Code:

Sub Macro2()

    ActiveSheet.ChartObjects("Chart 1").BringToFront
    
End Sub

where Chart 1 is a line chart that I have created.

And the second button has this macro

Code:

Sub Macro3()

ActiveSheet.ChartObjects("Chart 2").BringToFront

End Sub

And Chart 2 is a column chart.

This does work however I would like to use a listbox rather than all these buttons but I cannot figure out how to write one macro that will switch between the various charts. Anyone have any ideas on how to do this?

Thanks

Hi,

1. Create the list box.
2. Right click on the list box and select "Format Control".
3. In the "Input Range" specify a range of cells where you should have the values to show in the list box.
4. In "Cell Link" specify a cell where a value will be posted when you click the list box. "D1" in my example macro.
5. Right click on the list box and select "Assign Macro".
6. Enter the name of the macro. " ShowChart" in my example.

And here is the example macro. change the chart names / no. of charts as required:

Code:

Sub ShowChart()
Dim i As Integer
i = ActiveSheet.Range("D1").Value
Select Case i
  Case 1
    ActiveSheet.ChartObjects("Chart 1").BringToFront
  Case 2
    ActiveSheet.ChartObjects("Chart 2").BringToFront
  Case 3
    ActiveSheet.ChartObjects("Chart 3").BringToFront
End Select
End Sub

I hope I made myself clear.

One note: I am using Excel 2007. The procedure to format the control and assign a macro may be slightly different.

Regards.