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!

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" <(E-Mail Removed)> wrote in message
newsE831851-9CDB-4112-A870-(E-Mail Removed)...
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!

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" <(E-Mail Removed)> wrote in message
> newsE831851-9CDB-4112-A870-(E-Mail Removed)...
> 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!
>
> .
>

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

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
>
>
> .
>

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 Where:=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