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.<pre>
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"</pre>
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:<pre>
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</pre>
It should look like this when you are finished:<pre>
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</pre>
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