VBA Problem: Generated Graph named as Chart 1, hence VBA unable to work for second time the graph is generated

[Mcze77]

Hi Guys,

Im working on a spreadsheet whereby i have a set of values, and then i generate a bubble chart which is then re-formatted according to the correct font type and size that i want.

For every first time the bubble chart is generated and then reformatted , the code which is used is like this:
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.Legend.Select
With Selection.Format.TextFrame2.TextRange.Font
.NameComplexScript = "Arial Narrow"
.NameFarEast = "Arial Narrow"
.Size = 8
.Name = "Arial Narrow"

However, it wont work for the second time that i want to generate the graph as the code needs to be changed to Chart 2 for it to work.

Is there any way i can rename the chart to ensure that i can continually generate bubble charts after the first time?

Thanks in advance!

 

[Peter_SSs]

Welcome to the MrExcel board!

If you are only ever going to have one chart in that sheet, then try changing this line

ActiveSheet.ChartObjects(1).Activate

 

[Mcze77]

Welcome to the MrExcel board!

If you are only ever going to have one chart in that sheet, then try changing this line

ActiveSheet.ChartObjects(1).Activate

Hi Peter,

Thanks for the warm welcome!

I will have other graphs on the sheet actually.

Anyway when i use ActiveSheet.ChartObjects(1).Activate, i seem to lose the instructions below:

ActiveChart.Legend.Select
With Selection.Format.TextFrame2.TextRange.Font
.NameComplexScript = "Arial Narrow"
.NameFarEast = "Arial Narrow"
.Size = 8
.Name = "Arial Narrow"

Would you know how i can get around this? :D

 

[RoryA]

You should get a reference to the chart when you add it rather than relying on its name. You haven't posted the code you use to create it but charts.Add returns a reference to a chart object, or Shapes.Addchart.Chart will also work.

 

[Mcze77]

You should get a reference to the chart when you add it rather than relying on its name. You haven't posted the code you use to create it but charts.Add returns a reference to a chart object, or Shapes.Addchart.Chart will also work.

Thanks, but i still get the bug when i use the two formulas.

Anyway my code at the end is:

ActiveSheet.ChartObjects(1).Activate
ActiveChart.Legend.Select
With Selection.Format.TextFrame2.TextRange.Font
.NameComplexScript = "Arial Narrow"
.NameFarEast = "Arial Narrow"
.Size = 8
.Name = "Arial Narrow"

ActiveChart.Legend.Select
Selection.Top = 3.367
Selection.Height = 376.264
Selection.Height = 394.264

Im able to continually generate a new graph due to the ChartObjects(1) reference but the legend of the graph is not formatted accordingly to the font size and type instructions below... Would there be any reason why this is happening?

Thanks

 

[RoryA]

You haven't posted the code that creates the chart. I suspect your code is altering the wrong chart.

 

[Mcze77]

You haven't posted the code that creates the chart. I suspect your code is altering the wrong chart.

  Dim bubbleChart As ChartObject
  
  
Sub B()
 If (Selection.Columns.Count <> 4 Or Selection.Rows.Count < 3) Then
        MsgBox "Selection must have 4 columns and at least 2 rows"
        Exit Sub
    End If
    
  
    Set bubbleChart = ActiveSheet.ChartObjects.Add(Left:=Selection.Left, Width:=600, Top:=Selection.Top, Height:=400)
    bubbleChart.Chart.ChartType = xlBubble
    Dim r As Integer
    For r = 2 To Selection.Rows.Count
        With bubbleChart.Chart.SeriesCollection.NewSeries
            .Name = "=" & Selection.Cells(r, 1).Address(External:=True)
            .XValues = Selection.Cells(r, 2).Address(External:=True)
            .Values = Selection.Cells(r, 3).Address(External:=True)
            .BubbleSizes = Selection.Cells(r, 4).Address(External:=True)
        End With


    Next


    bubbleChart.Chart.SetElement (msoElementPrimaryCategoryAxisTitleAdjacentToAxis)
    bubbleChart.Chart.Axes(xlCategory, xlPrimary).AxisTitle.Text = "=" & Selection.Cells(1, 2).Address(External:=True)
    
    bubbleChart.Chart.SetElement (msoElementPrimaryValueAxisTitleRotated)
    bubbleChart.Chart.Axes(xlValue, xlPrimary).AxisTitle.Text = "=" & Selection.Cells(1, 3).Address(External:=True)
    
    bubbleChart.Chart.SetElement (msoElementPrimaryCategoryGridLinesMajor)
    bubbleChart.Chart.Axes(xlCategory).MinimumScale = 0
    bubbleChart.Chart.Axes(xlValue).MinimumScale = 0
    
    bubbleChart.Chart.PlotArea.Select
    
    ActiveChart.Shapes.AddTextbox(msoTextOrientationHorizontal, 18, 12.75, 98.25, _
        18.75).Select
    Selection.ShapeRange(1).TextFrame2.TextRange.Characters.Text = "High"
    Selection.ShapeRange(1).TextFrame2.TextRange.Characters(1, 4).ParagraphFormat. _
        FirstLineIndent = 0
    With Selection.ShapeRange(1).TextFrame2.TextRange.Characters(1, 4).Font
        .NameComplexScript = "+mn-cs"
        .NameFarEast = "+mn-ea"
        .Size = 11
        .Name = "+mn-lt"
    End With
    ActiveChart.Shapes.AddTextbox(msoTextOrientationHorizontal, 24.75, 50.25, 92.25 _
        , 21.75).Select
    Selection.ShapeRange(1).TextFrame2.TextRange.Characters.Text = "Low"
    Selection.ShapeRange(1).TextFrame2.TextRange.Characters(1, 3).ParagraphFormat. _
        FirstLineIndent = 0
    With Selection.ShapeRange(1).TextFrame2.TextRange.Characters(1, 3).Font
        .NameComplexScript = "+mn-cs"
        .NameFarEast = "+mn-ea"
        .Size = 11
        .Name = "+mn-lt"
    End With
    ActiveChart.Shapes.AddTextbox(msoTextOrientationHorizontal, 36.75, 93, 80.25, _
        17.25).Select
    Selection.ShapeRange(1).TextFrame2.TextRange.Characters.Text = "Difficult"
    Selection.ShapeRange(1).TextFrame2.TextRange.Characters(1, 9).ParagraphFormat. _
        FirstLineIndent = 0
    With Selection.ShapeRange(1).TextFrame2.TextRange.Characters(1, 9).Font
        .NameComplexScript = "+mn-cs"
        .NameFarEast = "+mn-ea"
        .Size = 11
        .Name = "+mn-lt"
    End With
    ActiveChart.Shapes.AddTextbox(msoTextOrientationHorizontal, 36, 136.5, 81, _
        17.25).Select
    Selection.ShapeRange(1).TextFrame2.TextRange.Characters.Text = "Easier"
    Selection.ShapeRange(1).TextFrame2.TextRange.Characters(1, 6).ParagraphFormat. _
        FirstLineIndent = 0
    With Selection.ShapeRange(1).TextFrame2.TextRange.Characters(1, 6).Font
        .NameComplexScript = "+mn-cs"
        .NameFarEast = "+mn-ea"
        .Size = 11
        .Name = "+mn-lt"
    End With
    ActiveChart.Shapes.Range(Array("TextBox 4")).Select
    Selection.ShapeRange.IncrementLeft 344.25
    Selection.ShapeRange.IncrementTop 238.5
    ActiveChart.Shapes.Range(Array("TextBox 3")).Select
    Selection.ShapeRange.IncrementLeft -6
    Selection.ShapeRange.IncrementTop 279.75
    ActiveChart.Shapes.Range(Array("TextBox 2")).Select
    Selection.ShapeRange.IncrementLeft -20.25
    Selection.ShapeRange.IncrementTop 274.5
    ActiveChart.ChartArea.Select
    ActiveChart.Shapes.Range(Array("TextBox 1")).Select
    Selection.ShapeRange.IncrementLeft -9
    Selection.ShapeRange.IncrementTop 14.25
    Selection.ShapeRange.IncrementRotation 270
    Selection.ShapeRange.IncrementLeft -41.25
    Selection.ShapeRange.IncrementTop 2.25
    ActiveChart.Shapes.Range(Array("TextBox 2")).Select
    Selection.ShapeRange.IncrementRotation 270
    Selection.ShapeRange.IncrementLeft -35.25
    ActiveChart.Shapes.Range(Array("TextBox 2", "TextBox 1")).Select
    Selection.ShapeRange.TextFrame2.TextRange.ParagraphFormat.Alignment = _
        msoAlignCenter
    ActiveChart.Shapes.Range(Array("TextBox 3")).Select
    ActiveChart.Shapes.Range(Array("TextBox 3", "TextBox 4")).Select
    Selection.ShapeRange.TextFrame2.TextRange.ParagraphFormat.Alignment = _
        msoAlignCenter
    Selection.ShapeRange.Align msoAlignTops, msoFalse
    ActiveChart.Shapes.Range(Array("TextBox 1")).Select
    ActiveChart.Shapes.Range(Array("TextBox 2")).Select
    ActiveChart.Shapes.Range(Array("TextBox 2", "TextBox 1")).Select
    Selection.ShapeRange.Align msoAlignLefts, msoFalse
    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.Legend.Select
    With Selection.Format.TextFrame2.TextRange.Font
        .NameComplexScript = "Arial Narrow"
        .NameFarEast = "Arial Narrow"
        .Size = 8
        .Name = "Arial Narrow"
    
    ActiveChart.Legend.Select
    Selection.Top = 3.367
    Selection.Height = 376.264
    Selection.Height = 394.264
    
   End With
    
End Sub

At the start, the graph is referenced as bubbleChart.Chart, then it becomes ActiveChart later as it is the graph that is generated.. Is this correct?

 

[RoryA]

Since you already have a reference, you should always refer to bubbleChart.chart and not active chart when you need to refer to the actual chart, or just bubbleChart when you need to refer to the container ChartObject.

 

[Mcze77]

I have managed to solve it by removing this line:

ActiveSheet.ChartObjects("Chart 1").Activate

Im now able to generate the ActiveChart according to the format i specified.
Many thanks for all your help and im glad to have joined this forum! :D