I'm attempting to create a chart that the range of data changes depending on a (drop down box) selection. The only way I've found to do this is to use the visual basic editor, using an example from Microsoft's Knowledge Base.

Basically, the X-axis values are the same for each possible chart, and there are 12 columns of Y-axis data - the column of data that is used for the chart depends on the drop down selection. At least, this is how I envision it working. Here's how I've tried it:

1. Start the VB editor.
2. On the Insert menu, click Module.
3. Before worrying about attaching the column selection to the drop down, I first attempted to manually select just the 1st column of Y-axis data to get things going. When I run this code, it just gives me a white box with the specified dimensions. Here's the code:

Sub CurveSelect()
' Select the 1st column of Y-axis data.
Worksheets("Curvedata").Range("B3:B42").Select
' I left the following command out because it was selecting all columns.
'Selection.CurrentRegion.Select
' Assign the address of the selected range of cells to a variable.
myrange = Selection.Address
' Assign the name of the active sheet to a variable. This line is
' used in order to allow a chart to be created on a separate chart
' sheet.
mysheetname = ActiveSheet.Name
' Add a chart to the active sheet.
ActiveSheet.ChartObjects.Add(125.25, 60, 301.5, 155.25).Select
' To create a chart on a separate chart sheet, remark out the
' previous line, and substitute the next line for the one above.
' Charts.Add
Application.CutCopyMode = False
' This line can best be written by recording a macro, and
' modifying the code generated by the Microsoft Excel Macro
' recorder.
ActiveChart.ChartWizard _
Source:=Sheets(mysheetname).Range(myrange), _
Gallery:=xlLine, Format:=4, PlotBy:=xlRows, _
CategoryLabels:=1, SeriesLabels:=1, HasLegend:=1, _
Title:="", CategoryTitle:="", _
ValueTitle:="", ExtraTitle:=""
End Sub