Chart via Combo Box

[Mike]

I have the following macros set up to govern the operation of a chart, which
driven from a Combo Box. I've taken the code from an example I found but
cannot get it to run properly for me. If anyone has any help as to what's
wrong with it, that would be much appreciated. Thanks very much in advance.

Cheers

Mike


The code is as follows:

[NB: the Combo Box, located on the 'Direct Charting' worksheet, has macro
"DropDown_Change" assigned to it, and the "View Chart" macro is assigned to
a button on the 'Direct Data' worksheet.]



Sub ViewChart()
' Button click event handler
Dim StartRow As Long

' Display the chart for cursor position, if possible
If ActiveCell.Row > 1 And ActiveCell.Row < 81 Then
StartRow = ActiveCell.Row
Else
StartRow = 2
End If

' Activate the Chart sheet
With Sheets("Direct Charting")
.Activate
End With

' Initialize the DropDown & update the chart
Charts(1).DropDowns(1).Value = StartRow - 1
Call UpdateChart(StartRow - 1)
End Sub




Sub DropDown_Change()
' NOTE: This dropdown is not an ActiveX control
' Update the chart, based on selected item
Dim ListIndex As Integer
ListIndex = Charts(1).DropDowns(1).Value
Call UpdateChart(ListIndex)
End Sub




Sub UpdateChart(Item)
' Updates the chart using the selected dropdown item
Dim TheChart As Chart
Dim DataSheet As Worksheet
Dim CatTitles As Range, SrcRange As Range
Dim SourceData As Range

Set TheChart = Sheets("Direct Charting")
Set DataSheet = Sheets("Direct Data")

With DataSheet
Set CatTitles = .Range("A2:E2")
Set SrcRange = .Range(.Cells(Item + 2, 1), _
.Cells(Item + 2, 5))
End With
Set SourceData = Union(CatTitles, SrcRange)

With TheChart
.SetSourceData Source:=SourceData, PlotBy:=xlRows
.ChartTitle.Left = TheChart.ChartArea.Left
.Deselect
End With
End Sub

 

[Jon Peltier]

Mike -

I have some examples on my web site that use the combobox without
requiring macros:

http://peltiertech.com/Excel/Charts/ChartByControl.html

- Jon