Activating a specific Chart

[kotzo]

Hi,

The code bellow, should do the following:

When the Workbook (Report_1.xls), worksheet(Problematic Stock) is for the first accessed in order to create initialy the graph (condition inde<=1) then along with the other things the graph is named "Problematic".

In any other case (after the first chart creation) (condition inde>1) the chart is only being updated.

My problem is that the code : Charts("Problematic").Activate is not working (subscript out of range).

Could you please help me ?

Tzovanis

[SIZE=1]Workbooks.Open "C:\Logistics Container\Reports_1.xls"
    Workbooks("Reports_1.xls").Activate
  Worksheets("Problematic Stock").Activate
Set Sh = Workbooks(backbone).Sheets("Data_Discont")
If inde <= 1 Then
   With Charts.Add
        .Name = "Problematic"
      .ChartType = xlLineMarkers
     .SeriesCollection.NewSeries
    .SeriesCollection(1).XValues = "=[" & backbone & "]Data_Discont!" & Sh.Range(Sh.Cells(4, 1), Sh.Cells(inde + 3, 1)).Address(, , xlR1C1)
    .SeriesCollection(1).Values = "=[" & backbone & "]Data_Discont!" & Sh.Range(Sh.Cells(4, 12), Sh.Cells(inde + 3, 12)).Address(, , xlR1C1)
      .HasTitle = True
       .HasLegend = False
       .ChartTitle.Text = "Discontinued Products"
      .Axes(xlCategory, xlPrimary).HasTitle = False
      .Axes(xlValue, xlPrimary).HasTitle = True
      .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Value"
      .Location Where:=xlLocationAsObject, Name:="Problematic"
   End With
   GoTo 7678
  End If
  
  Charts("Problematic").Activate
  With ActiveChart
.SeriesCollection(1).XValues = "=[" & backbone & "]Data_Discont!" & Sh.Range(Sh.Cells(4, 1), Sh.Cells(inde + 3, 1)).Address(, , xlR1C1)
.SeriesCollection(1).Values = "=[" & backbone & "]Data_Discont!" & Sh.Range(Sh.Cells(4, 12), Sh.Cells(inde + 3, 12)).Address(, , xlR1C1)
  End With
  
7678[/SIZE]

 

[vgreen]

Have you defined inde somewhere else?

 

[kotzo]

[SIZE=1]Dim inde As Integer[/SIZE]
[SIZE=1][/SIZE] 
[SIZE=1]inde = ActiveSheet.Cells(1, 1).Value[/SIZE]

The code is working without the part

[SIZE=1]  [B]Charts("Problematic").Activate[/B]
  With ActiveChart
.SeriesCollection(1).XValues = "=[" & backbone & "]Data_Discont!" & Sh.Range(Sh.Cells(4, 1), Sh.Cells(inde + 3, 1)).Address(, , xlR1C1)
.SeriesCollection(1).Values = "=[" & backbone & "]Data_Discont!" & Sh.Range(Sh.Cells(4, 12), Sh.Cells(inde + 3, 12)).Address(, , xlR1C1)
  End With
[/SIZE]

So the problem is not the inde, because I traced the value.

The errors appears at the bold line (Charts("Problematic").Activate)

 

[kotzo]

Hi,

any help ?

 

[Norie]

Why not create a reference to the chart when you create it?

Then you shouldn't need to use Activate or worry about the name in the code.

Something like this perhaps.

Dim wbOpen As Workbook
Dim cht As Chart
Dim sh As Worksheet
 
    Set wbOpen = Workbooks.Open("C:\Logistics Container\Reports_1.xls")
 
    Set sh = Workbooks(backbone).Sheets("Data_Discont")
 
    If inde <= 1 Then
        ' its unclear where the chart id to be added, so I've guessed the 'Reports_1.xls" workbook
        
        Set cht = wbOpen.Charts.Add
 
        With cht
            .Name = "Problematic"
            .ChartType = xlLineMarkers
            .SeriesCollection.NewSeries
            .SeriesCollection(1).XValues = "=[" & backbone & "]Data_Discont!" & sh.Range(sh.Cells(4, 1), sh.Cells(inde + 3, 1)).Address(, , xlR1C1)
            .SeriesCollection(1).Values = "=[" & backbone & "]Data_Discont!" & sh.Range(sh.Cells(4, 12), sh.Cells(inde + 3, 12)).Address(, , xlR1C1)
            .HasTitle = True
            .HasLegend = False
            .ChartTitle.Text = "Discontinued Products"
            .Axes(xlCategory, xlPrimary).HasTitle = False
            .Axes(xlValue, xlPrimary).HasTitle = True
            .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Value"
            .Location Where:=xlLocationAsObject, Name:="Problematic"
        End With
    End If
 
    With cht

        .SeriesCollection(1).XValues = "=[" & backbone & "]Data_Discont!" & sh.Range(sh.Cells(4, 1), sh.Cells(inde + 3, 1)).Address(, , xlR1C1)

        .SeriesCollection(1).Values = "=[" & backbone & "]Data_Discont!" & sh.Range(sh.Cells(4, 12), sh.Cells(inde + 3, 12)).Address(, , xlR1C1)

    End With

It's unlikely this code will work as is, there are some variables in the code that are obviously used/set in the previous code, it's not clear where the chart is going...

Also charts can be difficult to work with.

Hopefully though it'll at least illustrate what I mean.