Interactive Chart/ "Method Cells of object '_global' failed"

[Sibilia]

Hi all,

I have a chart with time period on axis. I want the user of this char
to be able to change this time period via a button.
here is the beginning of the macro link to the button. the macro bug
when I want the series to be defined depending on the calculated ro
numbers of the series (in bold in the macro).
The error message is "Method Cells of object '_global' failed"

Do anyone have a solution for this problem ?
Many thanks

Sibilia

Sub Axis_Graph()
Dim startYear As Integer
Dim EndYear As Integer
Dim startYear2 As Integer
Dim EndYear2 As Integer
Dim StartLine As Integer
Dim EndLine As Integer

startYear = 1950 ' default value
EndYear = 2050 ' default value
startYear2 = Sheets("Incidence").Range("H9").Value ' choose by th
user
EndYear2 = Sheets("Incidence").Range("J9").Value ' choose by the user

StartLine = 3 + (startYear2 - startYear) 'Calculte the row numbe
where is the StartYear2

EndLine = 103 - (EndYear2 - EndYear) 'Calculte the row number where i
the EndYear2

Sheets("Incidence").Activate
ActiveSheet.ChartObjects("Chart 32").Activate
*ActiveChart.SeriesCollection(1).XValues
ActiveSheet.Range(Cells(StartLine, "W"), Cells(EndLine, "W"))
'ActiveSheet.Range("W3:W103")*
....

 

[Rob Bovey]

Hi Sibilia,

It looks like your chart is active when you try to call the ActiveSheet
method. This will cause the error because a chart sheet doesn't have any
cells. Try modifying your code like so:

With Sheets("Incidence")
.ChartObjects("Chart 32").Chart.SeriesCollection(1).XValues = _
.Range(.Cells(StartLine, "W"), .Cells(EndLine, "W"))
End With

--
Rob Bovey, Excel MVP
Application Professionals
http://www.appspro.com/

* Take your Excel development skills to the next level.
* Professional Excel Development
http://www.appspro.com/Books/Books.htm

 

[Sibilia]

Great! It works!!!! but as the graph got x and Y Axis, I tried to
use the same code for the second axis ; So the code becomes:

With Sheets("Incidence")
..ChartObjects("Chart 32").Chart.SeriesCollection(1).XValues = _
..Range(.Cells(StartLine, "W"), .Cells(EndLine, "W"))

..ChartObjects("Chart 32").Chart.SeriesCollection(1).Values = _
..Range(.Cells(StartLine, "X"), .Cells(EndLine, "X"))
End With


But when I do this using F8, it is ok , it works. But when I link the
macro to a button then it stops with this message: Type mismatch.
Don't know what to do ... .

Many thanks anyway!

 

[Rob Bovey]

Hi Sibilia,

If you are using an ActiveX CommandButton, make sure you have its
TakeFocusOnClick property set to False.

--
Rob Bovey, Excel MVP
Application Professionals
http://www.appspro.com/

* Take your Excel development skills to the next level.
* Professional Excel Development
http://www.appspro.com/Books/Books.htm