Chart disappearing in Excel 07 but not in 03

[Henk]

In a sheet I have a base chart with a line graph of 26 countries. In range
U15:U40 the countries are listed with a simple data validation drop-down box
"Include/Exclude" right next to it. The moment a country is included or
excluded, a macro should run te recreate the chart by deleting the chart
which is on screen (which is, of course, not the base chart) and copying the
base chart to the top of the sheet (range B8, where the other chart is just
deleted). After that, all countries in the new chart should be deleted if
they are excluded.

The macro I wrote works in Excel 2003 and 2007 when more than 1 country is
included. If only one country is included it still works (a bit differently
!?) in 2003, but the chart area disappears in 2007

I have the following code :

Private Sub Worksheet_Change(ByVal Target As Range)
.........
If Target = "Include" Or Target = "Exclude" Then

Worksheets("Globals").Unprotect Password:="xxxxx"
Sheets("Globals").Range("ChangeMode").Value = False

Call GraphChange

Sheets("Globals").Range("ChangeMode").Value = True
Worksheets("Globals").Protect Password:="xxxxx"

End If
Sub GraphChange()

ActiveWindow.Zoom = 100
ActiveSheet.ChartObjects("Graph02").Activate
ActiveWindow.Visible = False
Selection.Delete
ActiveSheet.ChartObjects("Graph02Base").Activate
ActiveChart.ChartArea.Select
ActiveChart.ChartArea.Copy
ActiveWindow.Visible = False
Windows("Consolidation.xls").Activate
Range("B8").Select
ActiveSheet.Paste
Range("Graph02_Date_choice").Select
ActiveSheet.ChartObjects(2).Name = "Graph02"

Dim x As Integer
Dim z As Integer
Dim CheckRange As String

x = 15
z = 0

For y = 1 To 26

CheckRange = "V" & x

If Range(CheckRange).Value = "Exclude" Then

ActiveSheet.ChartObjects("Graph02").Activate
ActiveChart.SeriesCollection(y - z).ChartType = xlColumnClustered
ActiveChart.SeriesCollection(y - z).Delete

z = z + 1

End If

x = x + 1

Next y

Range("Graph02_Zoom").Select
ActiveWindow.Zoom = True

End Su
------------------------------------------------------------------------------------------
Anyone a clue?

Regards,

Henk

 

[exceluserforeman]

I was shocked to see the Dims half way through your code! They should always
be at the top, before the code starts.

For y = 1 To 26

CheckRange = "V" & x

What is the "V" for and why hasn't it been Dimensioned and what about the
"Y" ????

ActiveChart.SeriesCollection(y - z).ChartType = xlColumnClustered
ActiveChart.SeriesCollection(y - z).Delete

z=z+1


y & z will always be the same number.

At the start x=15
As far as I can see x doesn't do anything except x=x+1
Maybe they are typos when you submitted this example...

I am not an expert but I do not think 2007 will accept copy and paste of the
chart to a worksheet.

 

[Henk]

Dear Exceluserforeman,

Many thanks for your shocking and extremely helpful comments. If this is all
you can do for someone who is struggling with stranged differences between VB
code in 2003 and 2007 versions, then please keep your comments for yourself
in the future as far as I am concerned.

For your information, I have managed to find a workaround for this problem
in a, in your eyes probably very dirty way, but at least : It works. Wich can
not be said about the solution you send me.

Thanks again and best regards,

Henk