Changing data range for a chart by combo box

[Emlyn]

I have an Excel 2000 chart, with similar data arranged in about 50 columns of 8 rows. I would like to be able to see a column chart of, say, the first column, and then to choose from some kind of control any one of the other columns and have the chart change to show the new info. Am I whistling in the wind, or is this relatively easy? I would be grateful for some guidance.

[Tom Schreiner]

Hi Emlyn.
Yes it is very possible.
I decided to try it out and found several ways that you could do this.
The best way? I don't know...
But if you are wanting to list various source ranges for your chart data, in a combobox, here is how I would do it.
Create a simple list in some out of the way area on your sheet containing the names of your source ranges. You do not need to name the individual ranges, just a list of names of what you would like to refer to each source range. In the next column over type in the range address of the coresponding source range.
For example:
You list 50 names refering to 50 chart data source ranges in AA1:AA50, you name the first range in AA1 "Source One", In AB1 place the address such as A10:A30 as a text string.
You could place a combobox and set its ListFillRange property to AA1:AA50.
When a user chooses a name from your combobox, the related address in AB1 would be used to reset the chart's source data.
If you know how to do this, then good.
If you will need a code example, we will need:
All names and associated ranges, your charts name, ex. "Chart 1", ect...
Also, is your chart embedded in a worksheet or as it's own sheet?
Thanks,
Tom

[Emlyn]

Thanks for the very quick response, Tom.

I thought that I was quite good with this stuff, but it is fast becoming clear that I have a lot to learn. I have done as you suggested but have been unable to make the chart understand the (text)contents of a cell as a "data source range". What am I doing wrong?

My columns of data are d5:d11 through to am5:am11. I have Named these ranges "Question1,Question2, ..." and have the complete list in cells BA6 to BA41, withthe cell references alongside, in BB6 to BB41.

I don't care where my chart ends up, but I don't want to have to draw 36 separate charts. If you can, please help a little bit more!

Thanks

[Tom Schreiner]

Hi Emlyn...

Any problems and I can mail you the sheet in which I tested this on for your reference. You may need to edit some of this code.

Assumes sheet name = "Sheet1"

   "    chart is embedded in sheet1

   "    source ranges D5:D11,...,AM5:AM11

   "    List for combo box in BA6:BA41

   "    Source range addresses in BB6:BB41

   "    Combobox name = "ComboBox1"

Widen column(s) A, B, and or C to embed your chart. Draw out a combobox...
Not a form's combobox, mind you, but an ActiveX combo. Right-click on your worksheet menu bar, choose "ToolBox", click the triangle(Places you in design mode), click on the ComboBox Icon, draw it onto your screen. Rightclick on your new control and choose properties. Find ListFillRange and enter the range BA6:BA41.
Close the properties popup and double-click on the control. This should bring up the combobox's change event by default. Paste the following code exactly as listed:

Dim MyListRange As Range, FindRow, SourceRange As Range

Set MyListRange = Range("BA6:BA41")

FindRow = WorksheetFunction.Match( _

ComboBox1.Text, MyListRange, 0)

With Sheets("Sheet1")

    Set SourceRange = .Range(.Range("BB5").Offset(FindRow, 0))

    .ChartObjects(1).Activate

    ActiveChart.SetSourceData Source:= _

    SourceRange, PlotBy:=xlRows

    .Range("C4").Value = SourceRange.Address

    .Range("C4").Select

End With

It should look like this when you are finished:

Private Sub ComboBox1_Change()

Dim MyListRange As Range, FindRow, SourceRange As Range

Set MyListRange = Range("BA6:BA41")

FindRow = WorksheetFunction.Match( _

ComboBox1.Text, MyListRange, 0)

With Sheets("Sheet1")

    Set SourceRange = .Range(.Range("BB5").Offset(FindRow, 0))

    .ChartObjects(1).Activate

    ActiveChart.SetSourceData Source:= _

    SourceRange, PlotBy:=xlRows

    .Range("C4").Value = SourceRange.Address

    .Range("C4").Select

End With

End Sub

Click on the Triangle again to take you out of design mode.
You should be set from here.
If you need the sheet Email me:
Excel_Create@earthlink.net

Tom



[ This Message was edited by: TsTom on 2002-05-13 19:53 ]