I have a an XY Chart that I am using to graph a histogram. I want to make
the chart dynamic so that it pulls in multiple Bins/Counts based on an Active
X Dropdown box located on the chart. I have three sets of Bins/Count. The
Bins are located in: Sheet1!$H$24:$J$43. The Counts are located in:
Sheet1!$H$45:$J$64. Now I have named (in Excel) the first column in the Bins
("BinRange" = Sheet1!$H$24:$H$43). I have also named (in Excel) the first
column in the Count range ("CountRange" = Sheet1!$H$45:$H$64). The Active X
control has a list box which generates a list of 1,2,3. Now I want to use
the listbox to pick the correct column number (1 to 3), and then use this
with an offset to pully the correct Bin/Count data range into my XY graph. I
tried recording the following routine but cannot get it towork. What am I
doing wrong here? I have assigned the macro below to the list box. Thanks

Sub Macro2()
Dim X As Integer

X = Range("ColumnCounter") 'This value is generated by Listbox 1 to 3

ActiveChart.SeriesCollection(1).XValues = Range("BinRange").Offset(0, 0)
ActiveChart.SeriesCollection(1).Values = Range("CountRange").Offset(0, 0)
ActiveChart.SeriesCollection(1).Name = Range("ChartName")
Calculate

End Sub