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 Sub
------------------------------------------------------------------------------------------
Anyone a clue?

Regards,

Henk