Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Chart disappearing in Excel 07 but not in 03
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Chart disappearing in Excel 07 but not in 03
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Chart disappearing in Excel 07 but not in 03
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2007: disappearing chart sheets! | Setting up and Configuration of Excel | |||
Disappearing Data Label in chart | Charts and Charting in Excel | |||
Disappearing Chart Worksheet Names | Excel Discussion (Misc queries) | |||
Disappearing chart | Charts and Charting in Excel | |||
Disappearing Chart Axis | Charts and Charting in Excel |