If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Can not get Excel macros to save chart format -lost in space!
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! |
#2
|
|||
|
|||
Can not get Excel macros to save chart format -lost in space!
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/ChartsH...kChartVBA.html - Mike http://www.MikeMiddleton.com "AccessQuestion" wrote in message news 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! |
#3
|
|||
|
|||
Can not get Excel macros to save chart format -lost in space!
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/ChartsH...kChartVBA.html - Mike http://www.MikeMiddleton.com "AccessQuestion" wrote in message news 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! . |
#4
|
|||
|
|||
Can not get Excel macros to save chart format -lost in space!
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 |
#5
|
|||
|
|||
Can not get Excel macros to save chart format -lost in space!
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 . |
#6
|
|||
|
|||
:(((((( help??
Hi Mike and Ed,
Sorry for my slowness here ( thank you for your help and patience! I'm still having problems (( I feel really stupid here. Basically I have 2 macros, 806X and 807X. Macro806X just creates a simple bar chart for me. Macro806X works fine all the time. Macro807X is the 2nd macro after I run 806X. This macro simply just takes the chart that macro806X created and formats it. I've listed the code for both macros below. Also, I can't seem to find the name of the chart by doing CTL+click on the chart (which is its own worksheet). Not sure what I'm doing wrong. Sub Macro806X() ' ' Macro806X Macro ' Cells.Select Range("A1:AA40").AdvancedFilter Action:=xlFilterInPlace, Unique:=False ActiveSheet.Shapes.AddChart.Select ActiveChart.SetSourceData Source:=Range( _ "'Query18_Subtotal 13wk qty cross'!$1:$1048576") ActiveChart.ChartType = xlColumnClustered ActiveChart.PlotArea.Select ActiveChart.SetSourceData Source:=ActiveSheet.Range("A1:R30"), _ PlotBy:=xlRows ActiveSheet.ChartObjects(1).Activate ActiveChart.ChartArea.Select ActiveChart.Location Whe=xlLocationAsNewSheet ActiveChart.ApplyLayout (5) ActiveWorkbook.Save End Sub Sub Macro807X() ' ' Macro807X Macro ' ActiveSheet.ChartObjects("Chart 1").Activate ActiveSheet.ChartObjects("Chart 1").Activate ActiveChart.ChartTitle.Select ActiveSheet.ChartObjects("Chart 1").Activate ActiveChart.ChartTitle.Text = "13 Week By Qtry" ActiveSheet.ChartObjects("Chart 1").Activate ActiveChart.ChartArea.Select ActiveSheet.ChartObjects("Chart 1").Activate ActiveSheet.ChartObjects("Chart 1").Activate ActiveChart.ChartTitle.Select ActiveSheet.ChartObjects("Chart 1").Activate ActiveChart.ChartTitle.Text = "13 Week By Qty" ActiveSheet.ChartObjects("Chart 1").Activate ActiveChart.ChartArea.Select End Sub |
Thread Tools | |
Display Modes | |
|
|