find name of a chart

[inquirer]

I am writing some vba code in Excel 2003 to use the histogram function
to make histograms of data in a number of different worksheets.
I would like to delete all existing charts in a worksheet and then
create a histogram and then modify its properties. I have:

Application.Run
"ATPVBAEN.XLA!Histogram",ActiveSheet.Range("$D$2:$D$733") _
, ActiveSheet.Range("$K$2"), ActiveSheet.Range("$J$3:$J$21"),
False, False _
, True, False
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.ChartArea.Select
ActiveSheet.Shapes("Chart 1").ScaleHeight 2.98, msoFalse,
msoScaleFromTopLeft
ActiveChart.Legend.Select
Selection.Delete

If there is an existing chart in the worksheet, this fails at
ActiveSheet.ChartObjects("Chart 1").Activate

so I would like to know how to get the name of the chart created by the
histogram.

Could anyone help please?
Thanks
Chris

 

[Leith Ross]

I am writing some vba code in Excel 2003 to use the histogram function
to make histograms of data in a number of different worksheets.
I would like to delete all existing charts in a worksheet and then
create a histogram and then modify its properties. I have:

Application.Run
"ATPVBAEN.XLA!Histogram",ActiveSheet.Range("$D$2:$D$733") _
, ActiveSheet.Range("$K$2"), ActiveSheet.Range("$J$3:$J$21"),
False, False _
, True, False
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.ChartArea.Select
ActiveSheet.Shapes("Chart 1").ScaleHeight 2.98, msoFalse,
msoScaleFromTopLeft
ActiveChart.Legend.Select
Selection.Delete

If there is an existing chart in the worksheet, this fails at
ActiveSheet.ChartObjects("Chart 1").Activate

so I would like to know how to get the name of the chart created by the
histogram.

Could anyone help please?
Thanks
Chris

Hello Chris,

The new chart will always be the last one added to the collection.
Yoou can return the name like this...

N = ActiveSheet.ChartObjects.Count
ChrtName = ActiveSheet.ChartObjects(N).Name

Siincerely,
Leith Ross

 

[Jon Peltier]

If you're just removing thm all, use

ActiveSheet.ChartObjects.Delete

To make the ATP-related code run better, add a few lines to your code, right
after the Application.Run "ATPVBAEN.XLA!Histogram" line.

Dim sChtName As String
' unique name for chart object
sChtName = "HISTO_" & format(now,"yymmdd_hhmmss")
activesheet.chartobjects(activesheet.chartobjects.count).name = sChtname

Now you can reference the chart object using

ActiveSheet.ChartObjects(sChtname)

instead of

ActiveSheet.ChartObjects("Chart 1")

The macro recorder capturees all of your mouse clicks, so you can streamline
the codee a bit by making this kind of change:

Dim sChtName As String

Application.Run "ATPVBAEN.XLA!Histogram", _
ActiveSheet.Range("$D$2:$D$733"), _
ActiveSheet.Range("$K$2"), _
ActiveSheet.Range("$J$3:$J$21"), _
False, False, True, False

' unique name for chart object
sChtName = "HISTO_" & format(now,"yymmdd_hhmmss")
activesheet.chartobjects(activesheet.chartobjects.count).name = sChtname

With ActiveSheet.ChartObjects(sChtname)
.Height = .Height * 2.98
' I prefer to use actual dimensions, not scaling
With .Chart
.Legend.Delete
' othr chart formatting in here
End With
End With

It will run faster this way, without flashing of the screen.

See also my recent blog post,
http://peltiertech.com/WordPress/2008/03/11/how-to-fix-a-recorded-macro/

- Jon

 

[inquirer]

If you're just removing thm all, use

ActiveSheet.ChartObjects.Delete

To make the ATP-related code run better, add a few lines to your code, right
after the Application.Run "ATPVBAEN.XLA!Histogram" line.

Dim sChtName As String
' unique name for chart object
sChtName = "HISTO_" & format(now,"yymmdd_hhmmss")
activesheet.chartobjects(activesheet.chartobjects.count).name = sChtname

Now you can reference the chart object using

ActiveSheet.ChartObjects(sChtname)

instead of

ActiveSheet.ChartObjects("Chart 1")

The macro recorder capturees all of your mouse clicks, so you can streamline
the codee a bit by making this kind of change:

Dim sChtName As String

Application.Run "ATPVBAEN.XLA!Histogram", _
ActiveSheet.Range("$D$2:$D$733"), _
ActiveSheet.Range("$K$2"), _
ActiveSheet.Range("$J$3:$J$21"), _
False, False, True, False

' unique name for chart object
sChtName = "HISTO_" & format(now,"yymmdd_hhmmss")
activesheet.chartobjects(activesheet.chartobjects.count).name = sChtname

With ActiveSheet.ChartObjects(sChtname)
.Height = .Height * 2.98
' I prefer to use actual dimensions, not scaling
With .Chart
.Legend.Delete
' othr chart formatting in here
End With
End With

It will run faster this way, without flashing of the screen.

See also my recent blog post,
http://peltiertech.com/WordPress/2008/03/11/how-to-fix-a-recorded-macro/

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______

Thanks for your help. I have made th changes Jon suggested and it works
aok.
I have other formatting to do to the chart:

ActiveSheet.ChartObjects(sChtName).Activate
ActiveChart.ChartTitle.Select
Selection.Characters.Text = "Zone " & ws_name
Selection.AutoScaleFont = False
With Selection.Characters(Start:=1, Length:=4).Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 12
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
ActiveChart.Axes(xlCategory).AxisTitle.Select
Selection.Characters.Text = "RMR"
Selection.AutoScaleFont = False
With Selection.Characters(Start:=1, Length:=3).Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With

This works fine but I gather it would be more efficient to include it
in the With .Chart loop. I have tried to put it in there but without
success. Could you show me the syntax please?
Thanks
Chris

 

[Jon Peltier]

Put this within the With .Chart/End With block in the snippet I posted
earlier. Lines I've marked with ' * can probably be removed because theey
are restatements of default settings.

With .ChartTitle
.Characters.Text = "Zone " & ws_name
.AutoScaleFont = False
With .Characters(Start:=1, Length:=4).Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 12
.Strikethrough = False ' *
.Superscript = False ' *
.Subscript = False ' *
.OutlineFont = False ' *
.Shadow = False ' *
.Underline = xlUnderlineStyleNone ' *
.ColorIndex = xlAutomatic ' *
End With
End With
With .Axes(xlCategory).AxisTitle
.Characters.Text = "RMR"
.AutoScaleFont = False
With Selection.Characters(Start:=1, Length:=3).Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 10
.Strikethrough = False ' *
.Superscript = False ' *
.Subscript = False ' *
.OutlineFont = False ' *
.Shadow = False ' *
.Underline = xlUnderlineStyleNone ' *
.ColorIndex = xlAutomatic ' *
End With
End With

- Jon

 

[inquirer]

Many thanks, Jon. works well now