MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Changing series value for a chart. Totally Stumped

Posted by Mike on April 19, 2001 6:03 PM

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


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

Posted by Dave Hawley on April 19, 2001 7:13 PM

Hi Mike

I'm guessing you are using a CommandButton from the "Control ToolBox" Toolbar ? If you are all you need to do is set it's "TakeFocusOnClick" Property to false. Or use a button from the "Forms" Toolbar.


OzGrid Business Applications