Archive of Mr Excel Message Board

I've tried everything I can think of. I'm trying to use VBA to change a charts value range on the fly. I have a column of numbers on col 2 on sheet2. the simple line chart is on sheet1. The following code works great but only if I start the procedure (test4) within the VBE. It never works if I put a control button on sheet1 which simply calls the test4 procedure. I get an "Unable to set the values property of the series class" error EVERY time. Have to walk away from it for a while. Thanks.

Public Sub test4()
Dim k As Integer, num1 As Integer

Randomize

For k = 1 To 100'writes a bunch of nums to col 2
num1 = ((100) * Rnd)
Worksheets("sheet2").Cells(k, 2).Value = num1
Next k

For k = 1 To 5

Worksheets("Sheet1").ChartObjects(1). _
Chart.SeriesCollection(1).Values = Worksheets("sheet2").Range("B1:B5")
WaitTime (0.25)
Worksheets("Sheet1").ChartObjects(1). _
Chart.SeriesCollection(1).Values = Worksheets("sheet2").Range("B2:B6")
WaitTime (0.25)
Worksheets("Sheet1").ChartObjects(1). _
Chart.SeriesCollection(1).Values = Worksheets("sheet2").Range("B3:B7")
WaitTime (0.25)
Next k
End Sub