Jon
Sat Mar 15 23:11:50 CDT 2008
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" <noreply@noreply.com> wrote in message
news:47DC7140.20901@noreply.com...
> 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/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
>> _______
>>
>>
>> "inquirer" <noreply@noreply.com> wrote in message
>> news:47db46a0$0$20842$afc38c87@news.optusnet.com.au...
>>> 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