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
"exceluserforeman" wrote:
> 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" wrote:
>
> > 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
> >
> >
> >
> >
|
Similar Threads
