Excel 2007 : Excel 2007 VBA error

Hello

I hope someone can help me. I have a very simple piece of code, that works perfectly in Excel 2003, but my users have now upgraded to 2007 and the code doesn't work. I keep getting an error: Object variable not set (Error 91). But as far as I can see I have declared everything.

The code is:

Dim strWkNo As Integer
Dim strColNum As Integer

    strWkNo = InputBox("Which week number do you want to update to?", "Update Chart")
    
    strColNum = strWkNo + 1

    ActiveChart.SeriesCollection(1).XValues = "='Yields - Foam'!R3C2:R3C" & strColNum & ""
    ActiveChart.SeriesCollection(1).Values = "='Yields - Foam'!R5C2:R5C" & strColNum & ""
    ActiveChart.SeriesCollection(2).XValues = "='Yields - Foam'!R3C2:R3C" & strColNum & ""
    ActiveChart.SeriesCollection(2).Values = "='Yields - Foam'!R16C2:R16C" & strColNum & ""

The error message appears the first time I am using the variable for the activechart. Very confused , like I said it works in 2003. What do I need to do to get it working in 2007? Please help

Thanks Andy

The user is prompted to select the chart, because the sheet has loads of charts they need to select the chart that needs updating before they run the macro. I already have an error in place...

The full example of the code is:

Dim strWkNo As Integer
Dim strColNo As Integer

    strWkNo = InputBox("Which week number do you want to update to?", "Update Chart")
    strColNo = strWkNo + 1
    
    On Error GoTo ErrMsg
    
    ActiveChart.SeriesCollection(1).XValues = "=Forming!R31C2:R31C" & strColNo & ""
    ActiveChart.SeriesCollection(1).Values = "=Forming!R78C2:R78C" & strColNo & ""
    ActiveChart.SeriesCollection(2).XValues = "=Forming!R31C2:R31C" & strColNo & ""
    ActiveChart.SeriesCollection(2).Values = "=Forming!R77C2:R77C" & strColNo & ""
    
    Exit Sub
    
ErrMsg:
    MsgBox "You have not selected a chart - select the PC Forming Chart"