Excel VBA and Charts

[StuartJones]

I getting a "subscript out of range" error when i try and specify my chart as:

[tt]ActiveWorkbook.Charts(1).Activate[/tt]

I only have one chart in the workbook, and cannot find any other way of referencing it.


TIA,
Stuart

 

[xlbo]

Try recording yourself selecting the chart - that should give you the syntax you require

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[StuartJones]

Geoff,

I did that and the macro recorded was:
[tt]ActiveSheet.ChartObjects("Chart 3").Activate[/tt]

I then put the following code in my vb script:
[tt]
Worksheets(1).Activate
ActiveWorksheet.ChartObjects("Chart 3").Activate
[/tt]

and now I'm getting an "object required" error on the second line instead.

Cheers for your help,
Stuart

 

[TonyJollans]

Hi StuartJones,

If you have a ChartObject on a Sheet, you do NOT have any Charts in the Workbook!

I don't know how you are creating your chart or whether or not it is called "Chart 3", but if you only have the one, then referencing it by number should do ..

[blue]ActiveSheet.ChartObjects(1).Activate[/blue]

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[StuartJones]

thanks.

I just saw my mistake.... I had ActiveWorksheet, instead of ActiveSheet.


But now the .HasTitle doesn't want to work..

 

[SkipVought]

Hi,

The ChartObject has Chart as a Property

With ActiveSheet.ChartObjects(1).Chart
  .HasTitle = True
End With

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[StuartJones]

cheers Skip.

 

[StuartJones]

actually - i just tried that, and it failed...

[tt]
ActiveSheet.ChartObjects("Chart 3").Activate

With ActiveChart.Chart
.Type = xlLine
.HasTitle = True
.ChartTitle.Text = "January Sales"

numRows = Worksheets(2).Rows.Count

With .SeriesCollection
.Add Source = Worksheets(2).Range("C:C")


End With
End With
[/tt]
It didn't like the ActiveChart.Chart - the .HasTitle is a property of the ActiveChart object.

I'm running Excel 2002, if you need to know...

 

[SkipVought]

Stu,

this executes without error

Sub mychart()
    With ActiveSheet.ChartObjects(1).Chart
        .HasTitle = True
        .ChartTitle.Caption = "My Title"
    End With
End Sub

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[StuartJones]

I'm still getting a '1004' error message "Unable to set the HasTitle property of the Chart class'

 

[SkipVought]

I try NOT to activate and select. Generally it is NOT necessary and adds overhead. faq707-4105 How Can I Make My Code Run Faster?

Modifying your code...

    ActiveSheet.ChartObjects("Chart 3").Activate

    With ActiveChart
      .Type = xlLine
      .HasTitle = True
      .ChartTitle.Text = "January Sales"
      
      numRows = Worksheets(2).Rows.Count
      
      With .SeriesCollection
        .Add Source = Worksheets(2).Range("C:C")
        
        
      End With
    End With

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[StuartJones]

I still get the '1004' error with this code:

[tt]
Worksheets(1).Activate
ActiveSheet.ChartObjects("Chart 3").Activate

With ActiveChart
.Type = xlLine
[COLOR=red yellow].HasTitle = True[/color]
.ChartTitle.Text = "Historical Data"

With .SeriesCollection
.Add Source = Worksheets(2).Range("C:C")

End With
End With
[/tt]

 

[xlbo]

try this instead - I think the error comes from "Activechart" when what you really want to work on is the chartOBJECT:

Worksheets(1).Activate
'ActiveSheet.ChartObjects("Chart 3").Activate

with ActiveSheet.ChartObjects("Chart 3").CHART
.Type = xlLine
.HasTitle = True
.ChartTitle.Text = "Historical Data"

With .SeriesCollection
.Add Source = Worksheets(2).Range("C:C")

End With
End With


Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[StuartJones]

same problem - i'm pretty sure I've tried it already, but I did it again and same '1004' error.

 

[xlbo]

ok - lets get back to basics here. What KIND of chart is this ??

Is it
a: a chart embedded in a worksheet
b: a chart on it's own "chartsheet"
c: as above but a "Pivotchart"

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[StuartJones]

it's a chart that I created in the worksheet from the wizard.

 

[xlbo]

and it's definitely erroring on the .HasTitle line ??
I only ask because I have replicated your code and setup and run this code with no probs:

Worksheets(1).Activate
ActiveSheet.ChartObjects("Chart 3").Activate

With ActiveChart
.Type = xlLine
.HasTitle = True
.ChartTitle.Text = "Historical Data"

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[StuartJones]

i think the problem was because i did not complete the wizard properly.

my colleague has given me a recorded macro of the entire process which now works.

thanks for your help guys - sorry that i've been so dense