Can not get Excel macros to save chart format -lost in space!

[AccessQuestion]

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!

 

[Mike Middleton]

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


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]

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.

 

[Ed Ferrero]

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

 

[AccessQuestion]

Thank you very much Ed! I will try out what you mean as well.

 

[AccessQuestion]

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