Can not get Excel macros to save chart format -lost in space!
in [Charts]
|
Prev: Why do my pie charts have labels which say "add text"?
Next: Can not get Excel macros to format/save charts--help??? :(((((
From: AccessQuestion on 18 Mar 2010 15:07 Help??? I've been searching this forum for 5 days trying to find a problem similar to what I am having without any luck. I have been trying to use Excel macro to record the creation, formating and saving of a simple bar chart against my Excel data range but the macros do not run for the chart. I am able to creat the charts okay but the macro craps out when I run it. I keep getting " Set ActiveChart = ActiveSheet.ChartObjects(Chart1)" type errors as if it's not recognizing the chart that I just made. I am an advanced Excel person BUT not so with VB coding. All I am doing is selecting a data range, inserting a chart, selecting a new chart type, editing the chart label and then formating it to fit the page. I'm pulling my hair out! :((( I hope someone can help me? Thanks!
From: Mike Middleton on 18 Mar 2010 17:42 AccessQuestion - Excel VBA Help says "Use ChartObjects(index), where index is the embedded chart index number or name, to return a single object." So, maybe ChartObjects("Chart1") would work if you have named the chart "Chart1," or maybe ChartObjects(1) would work if its the first chart. I recommend Jon Peltier's web site for chart information. For your task, the following page may be useful: http://peltiertech.com/Excel/ChartsHowTo/QuickChartVBA.html - Mike http://www.MikeMiddleton.com "AccessQuestion" <AccessQuestion(a)discussions.microsoft.com> wrote in message news:DE831851-9CDB-4112-A870-BC569E46AF66(a)microsoft.com... Help??? I've been searching this forum for 5 days trying to find a problem similar to what I am having without any luck. I have been trying to use Excel macro to record the creation, formating and saving of a simple bar chart against my Excel data range but the macros do not run for the chart. I am able to creat the charts okay but the macro craps out when I run it. I keep getting " Set ActiveChart = ActiveSheet.ChartObjects(Chart1)" type errors as if it's not recognizing the chart that I just made. I am an advanced Excel person BUT not so with VB coding. All I am doing is selecting a data range, inserting a chart, selecting a new chart type, editing the chart label and then formating it to fit the page. I'm pulling my hair out! :((( I hope someone can help me? Thanks!
From: AccessQuestion on 19 Mar 2010 00:46 Thanks Mike for the help! Yes I did try those names as well but still get the same problem. I will check out Jon's website. -- mfg2529 "Mike Middleton" wrote: > AccessQuestion - > > Excel VBA Help says "Use ChartObjects(index), where index is the embedded > chart index number or name, to return a single object." > > So, maybe ChartObjects("Chart1") would work if you have named the chart > "Chart1," or maybe ChartObjects(1) would work if its the first chart. > > I recommend Jon Peltier's web site for chart information. For your task, the > following page may be useful: > > http://peltiertech.com/Excel/ChartsHowTo/QuickChartVBA.html > > - Mike > http://www.MikeMiddleton.com > > > "AccessQuestion" <AccessQuestion(a)discussions.microsoft.com> wrote in message > news:DE831851-9CDB-4112-A870-BC569E46AF66(a)microsoft.com... > Help??? I've been searching this forum for 5 days trying to find a problem > similar to what I am having without any luck. > > I have been trying to use Excel macro to record the creation, formating and > saving of a simple bar chart against my Excel data range but the macros do > not run for the chart. I am able to creat the charts okay but the macro > craps > out when I run it. > I keep getting " Set ActiveChart = ActiveSheet.ChartObjects(Chart1)" > type errors as if it's not recognizing the chart that I just made. > > I am an advanced Excel person BUT not so with VB coding. > > All I am doing is selecting a data range, inserting a chart, selecting a new > chart type, editing the chart label and then formating it to fit the page. > > I'm pulling my hair out! :((( I hope someone can help me? > Thanks! > > . >
From: Ed Ferrero on 19 Mar 2010 03:08 Hi, There is a difference in VBA between a Chart and a ChartObject. The latter is a sort of container for a Chart and can be embedded in a Worksheet. ActiveChart is a property of the ChartObject and is read-only. You can't write something like Set ActiveChart = MyObject. To use the Chart name instead of the index, find out what the chart name is (Ctrl-Click on the embedded chart and read the name of the Chart Object at the top left). Then enclose it in double-quotes in your code. Usually something like ..ChartObjects("Chart 1") not .ChartObjects(Chart1) Try running this bit of code to understand what is going on; Option Explicit ' good idea to always put this before any of your code Sub tst() Dim cht As Chart Dim oCht As ChartObject Set oCht = ActiveSheet.ChartObjects("Chart 1") Set cht = ActiveSheet.ChartObjects("Chart 1").Chart Debug.Print oCht.Name, cht.Name ' you can activate the ChartObject , you can't activate the chart oCht.Activate End Sub Ed Ferrero www.edferrero.com
From: AccessQuestion on 19 Mar 2010 10:06
Thank you very much Ed! I will try out what you mean as well. -- mfg2529 "Ed Ferrero" wrote: > Hi, > > There is a difference in VBA between a Chart and a ChartObject. The latter > is a sort of container for a Chart and can be embedded in a Worksheet. > > ActiveChart is a property of the ChartObject and is read-only. > You can't write something like Set ActiveChart = MyObject. > > To use the Chart name instead of the index, find out what the chart name is > (Ctrl-Click on the embedded chart and read the name of the Chart Object at > the top left). Then enclose it in double-quotes in your code. Usually > something like > ..ChartObjects("Chart 1") not .ChartObjects(Chart1) > > Try running this bit of code to understand what is going on; > > Option Explicit ' good idea to always put this before any of your > code > > Sub tst() > Dim cht As Chart > Dim oCht As ChartObject > > Set oCht = ActiveSheet.ChartObjects("Chart 1") > Set cht = ActiveSheet.ChartObjects("Chart 1").Chart > > Debug.Print oCht.Name, cht.Name > > ' you can activate the ChartObject , you can't activate the chart > oCht.Activate > > End Sub > > Ed Ferrero > www.edferrero.com > > > . > |