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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"inquirer" wrote in message
...
Jon Peltier wrote:
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/200...ecorded-macro/

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


"inquirer" wrote in message
u...
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


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