Excel Macro for creating XY scatter chart of named ranges

Factual Questions
1

I have a sheet with multiple columns that have data in them. They are named such that the cells in A2:A2500 have a name. I have a macro that gets input from a userform and stores the two selected name ranges in xvar and yvar, then uses the series collection method to add these two series to an xy scatter chart. The macro will work a few times then stop working. I think when it stops working, it will never work again, even if the workbook is closed, computer restarted, etc.

The error I get is:

Run time error 1004:
Method ‘Series Collection’ of object ‘_Chart’ failed



Private Sub cmdOK_Click()

'gets the data from the user form and creates
'a new XY scatterplot chart with all data from two user-selected columns

    Dim ws As Worksheet
    Dim xvar As Variant
    Dim yvar As Variant
    Dim chartname As String

'make sure the sheet is active
'get the names of the ranges for the x and y variables

    xvar = lbXvar
    yvar = lbYvar
    chartname = txtbxChartName
    Set ws = Worksheets("Combined Table")
   
'create and format the chart
        Charts.Add
    With ActiveChart
        .ChartType = xlXYScatter
        .SeriesCollection(1).XValues = "='SW Daily Digester Status.xls'!" & xvar
        .SeriesCollection(1).Values = "='SW Daily Digester Status.xls'!" & yvar
        .Location Where:=xlLocationAsNewSheet

The rest of the code is basically set up to format the chart, then I unload the form. I have set this up for now with a case select workaround, so that each value of xvar has a range of cells and is tested for sequentially, but there are 30 columns, so this is far from an elegant solution. Any help is very appreciated.

2

Don’t know if this is relevant, but I use the XY Chart Labeler for scatterploting in Excel, available here.
http://www.appspro.com/Utilities/ChartLabeler.htm
Maybe you could somehow integrate it.

3

Dunno about the PC, but on the mac you can set a breakpoint in the macro editor by clicking at the left edge of the window containing the macro. When you call the macro, it will stop at the breakpoint. Then you can single-step through it to see where the error is. Let me know if this works.

w.