Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I modify charts thorugh VB
I have a spreadsheet with ~120 sheets each with one chart. I recently
modified the chart and copied the new chart to each sheet using VB to copy. unfortunately, I realized that the sourcedata is the same sheet for all charts. I now want to change the source data for each sheet to the range below. This code executes but when i check the charts, the sourcedata still refers to the 1st sheet. What am I doing wrong? For each ws1 in Worksheets Set ch1 = ws1.ChartObjects.Item(1).Chart ch1.SetSourceData Source:=Sheets(ws1.Name).Range("E84:F85"), PlotBy _ :=xlColumns |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I modify charts thorugh VB
This worked for me (I changed the range to match my example):
Sub ChangeSourceSheet() Dim ws1 As Worksheet Dim ch1 As Chart For Each ws1 In Worksheets Set ch1 = ws1.ChartObjects.Item(1).Chart ch1.SetSourceData Source:=Sheets(ws1.Name).Range("A4:B20"), PlotBy _ :=xlColumns Next End Sub Are you sure you don't need to change ChartObjects(2)? - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Rayo K" wrote in message ... I have a spreadsheet with ~120 sheets each with one chart. I recently modified the chart and copied the new chart to each sheet using VB to copy. unfortunately, I realized that the sourcedata is the same sheet for all charts. I now want to change the source data for each sheet to the range below. This code executes but when i check the charts, the sourcedata still refers to the 1st sheet. What am I doing wrong? For each ws1 in Worksheets Set ch1 = ws1.ChartObjects.Item(1).Chart ch1.SetSourceData Source:=Sheets(ws1.Name).Range("E84:F85"), PlotBy _ :=xlColumns |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I modify charts thorugh VB
That's a good question. I don't understnad the embedded chart object model.
How would I know which chartobject corresponds to my chart? I chose chartobjects(1) because I only have one chart. But I have no way of knowing whether my chart has a different index number, at least not as far as I can tell. At any rate, the code you used isn't working for me. Maybe my chart has some other index number, but how can I know what it is? "Jon Peltier" wrote: This worked for me (I changed the range to match my example): Sub ChangeSourceSheet() Dim ws1 As Worksheet Dim ch1 As Chart For Each ws1 In Worksheets Set ch1 = ws1.ChartObjects.Item(1).Chart ch1.SetSourceData Source:=Sheets(ws1.Name).Range("A4:B20"), PlotBy _ :=xlColumns Next End Sub Are you sure you don't need to change ChartObjects(2)? - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Rayo K" wrote in message ... I have a spreadsheet with ~120 sheets each with one chart. I recently modified the chart and copied the new chart to each sheet using VB to copy. unfortunately, I realized that the sourcedata is the same sheet for all charts. I now want to change the source data for each sheet to the range below. This code executes but when i check the charts, the sourcedata still refers to the 1st sheet. What am I doing wrong? For each ws1 in Worksheets Set ch1 = ws1.ChartObjects.Item(1).Chart ch1.SetSourceData Source:=Sheets(ws1.Name).Range("E84:F85"), PlotBy _ :=xlColumns |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I modify charts thorugh VB
If you have one chart, then it is ChartObjects(1). You can see a chart
object's name by holding shift while clicking on the chart, then looking in the name box (above the row numbers). To list all of the charts, go to the VB Editor and open the Immediate Window. Paste this line, click the cursor back into the line, and press enter: for i = 1 to activesheet.chartobjects.count : ? i, activesheet.chartobjects(i).name : next this gives you a numerical list of the chart objects in the sheet along with their names. You can get the active chart's name by using this line in the Immediate Window: ? activechart.parent.name Has the series formula of the chart been updated to reflect the new source data sheet? - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Rayo K" wrote in message ... That's a good question. I don't understnad the embedded chart object model. How would I know which chartobject corresponds to my chart? I chose chartobjects(1) because I only have one chart. But I have no way of knowing whether my chart has a different index number, at least not as far as I can tell. At any rate, the code you used isn't working for me. Maybe my chart has some other index number, but how can I know what it is? "Jon Peltier" wrote: This worked for me (I changed the range to match my example): Sub ChangeSourceSheet() Dim ws1 As Worksheet Dim ch1 As Chart For Each ws1 In Worksheets Set ch1 = ws1.ChartObjects.Item(1).Chart ch1.SetSourceData Source:=Sheets(ws1.Name).Range("A4:B20"), PlotBy _ :=xlColumns Next End Sub Are you sure you don't need to change ChartObjects(2)? - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Rayo K" wrote in message ... I have a spreadsheet with ~120 sheets each with one chart. I recently modified the chart and copied the new chart to each sheet using VB to copy. unfortunately, I realized that the sourcedata is the same sheet for all charts. I now want to change the source data for each sheet to the range below. This code executes but when i check the charts, the sourcedata still refers to the 1st sheet. What am I doing wrong? For each ws1 in Worksheets Set ch1 = ws1.ChartObjects.Item(1).Chart ch1.SetSourceData Source:=Sheets(ws1.Name).Range("E84:F85"), PlotBy _ :=xlColumns |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I modify charts thorugh VB
It still does not work. The sourcedata remains unchanged. When I tried the
code for hte immediate window, it gave an error saying there was a "Next without for" error. When I tried entering "activesheet.chartobjects.count" it said the count method failed. "Jon Peltier" wrote: If you have one chart, then it is ChartObjects(1). You can see a chart object's name by holding shift while clicking on the chart, then looking in the name box (above the row numbers). To list all of the charts, go to the VB Editor and open the Immediate Window. Paste this line, click the cursor back into the line, and press enter: for i = 1 to activesheet.chartobjects.count : ? i, activesheet.chartobjects(i).name : next this gives you a numerical list of the chart objects in the sheet along with their names. You can get the active chart's name by using this line in the Immediate Window: ? activechart.parent.name Has the series formula of the chart been updated to reflect the new source data sheet? - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Rayo K" wrote in message ... That's a good question. I don't understnad the embedded chart object model. How would I know which chartobject corresponds to my chart? I chose chartobjects(1) because I only have one chart. But I have no way of knowing whether my chart has a different index number, at least not as far as I can tell. At any rate, the code you used isn't working for me. Maybe my chart has some other index number, but how can I know what it is? "Jon Peltier" wrote: This worked for me (I changed the range to match my example): Sub ChangeSourceSheet() Dim ws1 As Worksheet Dim ch1 As Chart For Each ws1 In Worksheets Set ch1 = ws1.ChartObjects.Item(1).Chart ch1.SetSourceData Source:=Sheets(ws1.Name).Range("A4:B20"), PlotBy _ :=xlColumns Next End Sub Are you sure you don't need to change ChartObjects(2)? - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Rayo K" wrote in message ... I have a spreadsheet with ~120 sheets each with one chart. I recently modified the chart and copied the new chart to each sheet using VB to copy. unfortunately, I realized that the sourcedata is the same sheet for all charts. I now want to change the source data for each sheet to the range below. This code executes but when i check the charts, the sourcedata still refers to the 1st sheet. What am I doing wrong? For each ws1 in Worksheets Set ch1 = ws1.ChartObjects.Item(1).Chart ch1.SetSourceData Source:=Sheets(ws1.Name).Range("E84:F85"), PlotBy _ :=xlColumns |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I modify charts thorugh VB
Something's wrong. Even if there are no charts, ChartObjects.Count should
return zero. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Rayo K" wrote in message ... It still does not work. The sourcedata remains unchanged. When I tried the code for hte immediate window, it gave an error saying there was a "Next without for" error. When I tried entering "activesheet.chartobjects.count" it said the count method failed. "Jon Peltier" wrote: If you have one chart, then it is ChartObjects(1). You can see a chart object's name by holding shift while clicking on the chart, then looking in the name box (above the row numbers). To list all of the charts, go to the VB Editor and open the Immediate Window. Paste this line, click the cursor back into the line, and press enter: for i = 1 to activesheet.chartobjects.count : ? i, activesheet.chartobjects(i).name : next this gives you a numerical list of the chart objects in the sheet along with their names. You can get the active chart's name by using this line in the Immediate Window: ? activechart.parent.name Has the series formula of the chart been updated to reflect the new source data sheet? - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Rayo K" wrote in message ... That's a good question. I don't understnad the embedded chart object model. How would I know which chartobject corresponds to my chart? I chose chartobjects(1) because I only have one chart. But I have no way of knowing whether my chart has a different index number, at least not as far as I can tell. At any rate, the code you used isn't working for me. Maybe my chart has some other index number, but how can I know what it is? "Jon Peltier" wrote: This worked for me (I changed the range to match my example): Sub ChangeSourceSheet() Dim ws1 As Worksheet Dim ch1 As Chart For Each ws1 In Worksheets Set ch1 = ws1.ChartObjects.Item(1).Chart ch1.SetSourceData Source:=Sheets(ws1.Name).Range("A4:B20"), PlotBy _ :=xlColumns Next End Sub Are you sure you don't need to change ChartObjects(2)? - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Rayo K" wrote in message ... I have a spreadsheet with ~120 sheets each with one chart. I recently modified the chart and copied the new chart to each sheet using VB to copy. unfortunately, I realized that the sourcedata is the same sheet for all charts. I now want to change the source data for each sheet to the range below. This code executes but when i check the charts, the sourcedata still refers to the 1st sheet. What am I doing wrong? For each ws1 in Worksheets Set ch1 = ws1.ChartObjects.Item(1).Chart ch1.SetSourceData Source:=Sheets(ws1.Name).Range("E84:F85"), PlotBy _ :=xlColumns |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I modify charts thorugh VB
It still does not work. The sourcedata remains unchanged. When I tried the
code for hte immediate window, it gave an error saying there was a "Next without for" error. Did you unwrap the two lines bfore pressing enter. When I tried entering "activesheet.chartobjects.count" it said the count method failed. Did you preceed with a ? (I havn't looked at your original problem) Regards, Peter T "Rayo K" wrote in message ... It still does not work. The sourcedata remains unchanged. When I tried the code for hte immediate window, it gave an error saying there was a "Next without for" error. When I tried entering "activesheet.chartobjects.count" it said the count method failed. "Jon Peltier" wrote: If you have one chart, then it is ChartObjects(1). You can see a chart object's name by holding shift while clicking on the chart, then looking in the name box (above the row numbers). To list all of the charts, go to the VB Editor and open the Immediate Window. Paste this line, click the cursor back into the line, and press enter: for i = 1 to activesheet.chartobjects.count : ? i, activesheet.chartobjects(i).name : next this gives you a numerical list of the chart objects in the sheet along with their names. You can get the active chart's name by using this line in the Immediate Window: ? activechart.parent.name Has the series formula of the chart been updated to reflect the new source data sheet? - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Rayo K" wrote in message ... That's a good question. I don't understnad the embedded chart object model. How would I know which chartobject corresponds to my chart? I chose chartobjects(1) because I only have one chart. But I have no way of knowing whether my chart has a different index number, at least not as far as I can tell. At any rate, the code you used isn't working for me. Maybe my chart has some other index number, but how can I know what it is? "Jon Peltier" wrote: This worked for me (I changed the range to match my example): Sub ChangeSourceSheet() Dim ws1 As Worksheet Dim ch1 As Chart For Each ws1 In Worksheets Set ch1 = ws1.ChartObjects.Item(1).Chart ch1.SetSourceData Source:=Sheets(ws1.Name).Range("A4:B20"), PlotBy _ :=xlColumns Next End Sub Are you sure you don't need to change ChartObjects(2)? - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Rayo K" wrote in message ... I have a spreadsheet with ~120 sheets each with one chart. I recently modified the chart and copied the new chart to each sheet using VB to copy. unfortunately, I realized that the sourcedata is the same sheet for all charts. I now want to change the source data for each sheet to the range below. This code executes but when i check the charts, the sourcedata still refers to the 1st sheet. What am I doing wrong? For each ws1 in Worksheets Set ch1 = ws1.ChartObjects.Item(1).Chart ch1.SetSourceData Source:=Sheets(ws1.Name).Range("E84:F85"), PlotBy _ :=xlColumns |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I modify charts thorugh VB
OK,
I tried "msgbox sheets("17th-3").chartobjects.count" in the immediate window, and it showed "2" The problem is, there is only one chart on that sheet. I tried another one and it showed 6. I think there were probably old charts that were cut or deleted that are still there somehow. Is there a way to clear out the old charts, and if not, how can I identify which chart is still on the sheet? "Jon Peltier" wrote: Something's wrong. Even if there are no charts, ChartObjects.Count should return zero. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Rayo K" wrote in message ... It still does not work. The sourcedata remains unchanged. When I tried the code for hte immediate window, it gave an error saying there was a "Next without for" error. When I tried entering "activesheet.chartobjects.count" it said the count method failed. "Jon Peltier" wrote: If you have one chart, then it is ChartObjects(1). You can see a chart object's name by holding shift while clicking on the chart, then looking in the name box (above the row numbers). To list all of the charts, go to the VB Editor and open the Immediate Window. Paste this line, click the cursor back into the line, and press enter: for i = 1 to activesheet.chartobjects.count : ? i, activesheet.chartobjects(i).name : next this gives you a numerical list of the chart objects in the sheet along with their names. You can get the active chart's name by using this line in the Immediate Window: ? activechart.parent.name Has the series formula of the chart been updated to reflect the new source data sheet? - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Rayo K" wrote in message ... That's a good question. I don't understnad the embedded chart object model. How would I know which chartobject corresponds to my chart? I chose chartobjects(1) because I only have one chart. But I have no way of knowing whether my chart has a different index number, at least not as far as I can tell. At any rate, the code you used isn't working for me. Maybe my chart has some other index number, but how can I know what it is? "Jon Peltier" wrote: This worked for me (I changed the range to match my example): Sub ChangeSourceSheet() Dim ws1 As Worksheet Dim ch1 As Chart For Each ws1 In Worksheets Set ch1 = ws1.ChartObjects.Item(1).Chart ch1.SetSourceData Source:=Sheets(ws1.Name).Range("A4:B20"), PlotBy _ :=xlColumns Next End Sub Are you sure you don't need to change ChartObjects(2)? - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Rayo K" wrote in message ... I have a spreadsheet with ~120 sheets each with one chart. I recently modified the chart and copied the new chart to each sheet using VB to copy. unfortunately, I realized that the sourcedata is the same sheet for all charts. I now want to change the source data for each sheet to the range below. This code executes but when i check the charts, the sourcedata still refers to the 1st sheet. What am I doing wrong? For each ws1 in Worksheets Set ch1 = ws1.ChartObjects.Item(1).Chart ch1.SetSourceData Source:=Sheets(ws1.Name).Range("E84:F85"), PlotBy _ :=xlColumns |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I modify charts thorugh VB
In Excel 2003 and maybe 2002, there's a button you can add to the Drawing
Toolbar. Click the arrow thingie on the right edge the Drawing toolbar, click Add or Remove Buttons, Drawing, and choose Select Multiple Items. Click on the new button that appears on the Drawing toolbar, and it will give you a list of all the objects in the sheet. When you delete columns or rows from behind a chart, the chart gets thinner, becoming a one-pixel wide chart lined up along cell boundaries or along the top or left edge of the sheet, where not even Superman could see it. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Rayo K" wrote in message ... OK, I tried "msgbox sheets("17th-3").chartobjects.count" in the immediate window, and it showed "2" The problem is, there is only one chart on that sheet. I tried another one and it showed 6. I think there were probably old charts that were cut or deleted that are still there somehow. Is there a way to clear out the old charts, and if not, how can I identify which chart is still on the sheet? "Jon Peltier" wrote: Something's wrong. Even if there are no charts, ChartObjects.Count should return zero. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Rayo K" wrote in message ... It still does not work. The sourcedata remains unchanged. When I tried the code for hte immediate window, it gave an error saying there was a "Next without for" error. When I tried entering "activesheet.chartobjects.count" it said the count method failed. "Jon Peltier" wrote: If you have one chart, then it is ChartObjects(1). You can see a chart object's name by holding shift while clicking on the chart, then looking in the name box (above the row numbers). To list all of the charts, go to the VB Editor and open the Immediate Window. Paste this line, click the cursor back into the line, and press enter: for i = 1 to activesheet.chartobjects.count : ? i, activesheet.chartobjects(i).name : next this gives you a numerical list of the chart objects in the sheet along with their names. You can get the active chart's name by using this line in the Immediate Window: ? activechart.parent.name Has the series formula of the chart been updated to reflect the new source data sheet? - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Rayo K" wrote in message ... That's a good question. I don't understnad the embedded chart object model. How would I know which chartobject corresponds to my chart? I chose chartobjects(1) because I only have one chart. But I have no way of knowing whether my chart has a different index number, at least not as far as I can tell. At any rate, the code you used isn't working for me. Maybe my chart has some other index number, but how can I know what it is? "Jon Peltier" wrote: This worked for me (I changed the range to match my example): Sub ChangeSourceSheet() Dim ws1 As Worksheet Dim ch1 As Chart For Each ws1 In Worksheets Set ch1 = ws1.ChartObjects.Item(1).Chart ch1.SetSourceData Source:=Sheets(ws1.Name).Range("A4:B20"), PlotBy _ :=xlColumns Next End Sub Are you sure you don't need to change ChartObjects(2)? - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Rayo K" wrote in message ... I have a spreadsheet with ~120 sheets each with one chart. I recently modified the chart and copied the new chart to each sheet using VB to copy. unfortunately, I realized that the sourcedata is the same sheet for all charts. I now want to change the source data for each sheet to the range below. This code executes but when i check the charts, the sourcedata still refers to the 1st sheet. What am I doing wrong? For each ws1 in Worksheets Set ch1 = ws1.ChartObjects.Item(1).Chart ch1.SetSourceData Source:=Sheets(ws1.Name).Range("E84:F85"), PlotBy _ :=xlColumns |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I modify charts thorugh VB
To determine the name of the active chart, select it and click 'view' on the
toolbar and select 'Chart Window'. "Peter T" wrote: It still does not work. The sourcedata remains unchanged. When I tried the code for hte immediate window, it gave an error saying there was a "Next without for" error. Did you unwrap the two lines bfore pressing enter. When I tried entering "activesheet.chartobjects.count" it said the count method failed. Did you preceed with a ? (I havn't looked at your original problem) Regards, Peter T "Rayo K" wrote in message ... It still does not work. The sourcedata remains unchanged. When I tried the code for hte immediate window, it gave an error saying there was a "Next without for" error. When I tried entering "activesheet.chartobjects.count" it said the count method failed. "Jon Peltier" wrote: If you have one chart, then it is ChartObjects(1). You can see a chart object's name by holding shift while clicking on the chart, then looking in the name box (above the row numbers). To list all of the charts, go to the VB Editor and open the Immediate Window. Paste this line, click the cursor back into the line, and press enter: for i = 1 to activesheet.chartobjects.count : ? i, activesheet.chartobjects(i).name : next this gives you a numerical list of the chart objects in the sheet along with their names. You can get the active chart's name by using this line in the Immediate Window: ? activechart.parent.name Has the series formula of the chart been updated to reflect the new source data sheet? - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Rayo K" wrote in message ... That's a good question. I don't understnad the embedded chart object model. How would I know which chartobject corresponds to my chart? I chose chartobjects(1) because I only have one chart. But I have no way of knowing whether my chart has a different index number, at least not as far as I can tell. At any rate, the code you used isn't working for me. Maybe my chart has some other index number, but how can I know what it is? "Jon Peltier" wrote: This worked for me (I changed the range to match my example): Sub ChangeSourceSheet() Dim ws1 As Worksheet Dim ch1 As Chart For Each ws1 In Worksheets Set ch1 = ws1.ChartObjects.Item(1).Chart ch1.SetSourceData Source:=Sheets(ws1.Name).Range("A4:B20"), PlotBy _ :=xlColumns Next End Sub Are you sure you don't need to change ChartObjects(2)? - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Rayo K" wrote in message ... I have a spreadsheet with ~120 sheets each with one chart. I recently modified the chart and copied the new chart to each sheet using VB to copy. unfortunately, I realized that the sourcedata is the same sheet for all charts. I now want to change the source data for each sheet to the range below. This code executes but when i check the charts, the sourcedata still refers to the 1st sheet. What am I doing wrong? For each ws1 in Worksheets Set ch1 = ws1.ChartObjects.Item(1).Chart ch1.SetSourceData Source:=Sheets(ws1.Name).Range("E84:F85"), PlotBy _ :=xlColumns |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can my colleagues modify my charts? | Charts and Charting in Excel | |||
routing email thorugh excel | Excel Discussion (Misc queries) | |||
link excel charts to web pages and update charts automatically | Charts and Charting in Excel | |||
Modify a UDF please? | Excel Worksheet Functions | |||
Modify this a bit | Excel Programming |