CODEWITHSUNDEEP
excelvba
Greg
Greg

Reputation: 147

Why does my VBA code work only when a MsgBox is added?

EDIT: I believe my error message "Maximum number of data series per chart is 255" was related to the range selection I had before calling the AddChart2 method. I still don't know why it previously said that my Chart did not have a title, even though I declared .HasTitle to be true. Consider this question resolved for now, although I'd still like to know why it wasn't working previously.

Also in the actual subroutine I'm using, pre-existing chart objects are deleted before it gets to this code, so there's not a problem with the ChartObjects(1) index.

The success of the code below determines entirely on whether or not I include the MsgBox function. Additionally, it only seems to work when I pass this specific argument to the MsgBox (Typing "MsgBox 1" does not work, even though there is one ChartObject on the page). Does anyone know why this is?

ActiveSheet.Shapes.AddChart2(227, xlLine).Select
ActiveChart.SetSourceData Source:=Range("$M2:$M" & CStr(Cells(Rows.Count, 13).End(xlUp).Row))

'MsgBox ActiveSheet.ChartObjects.Count

With Sheets("blah").ChartObjects(1).Chart
    .HasTitle = True
End With

MsgBox Sheets("blah").ChartObjects(1).Chart.HasTitle   ' (always returns True)

Sheets("blah").ChartObjects(1).Chart.ChartTitle.Text = "bleh"

When the code works, I get a chart with title as intended. When it does not work, I get an error message that the chart does not have a title.

Upvotes: 0

Views: 339

Answers (1)

StoneGiant
StoneGiant

Reputation: 1497

Assuming ActiveSheet is Sheet("blah"), try this...

Dim theChart As ChartObject ' Reference the new or existing chart
Dim sourceRange As Range    ' Chart's data source

' Create or attach to the chart and get the chart's source data range
With Sheets("blah")

    ' Create the chart if it doesn't exist
    If .ChartObjects.Count = 0 Then
        .Shapes.AddChart2 227, xlLine
    End If

    ' Grab a pointer to the chart
    Set theChart = .ChartObjects(1)

    ' Grab a pointer to the source range while inside the sheet's WITH block
    Set sourceRange = .Range("$M2:$M" & CStr(.Cells(.Rows.Count, 13).End(xlUp).Row))

End With

' Set the chart up.
With theChart.Chart
    .SetSourceData source:=sourceRange
    .HasTitle = True
    .ChartTitle.Text = "bleh"
End With

' Clean up
Set theChart = Nothing
Set sourceRange = Nothing

EDIT: The original code errored when I tested it. This has been tested in Excel 2016 and works. Also added comments to the code for clarification.

Upvotes: 1

Related Questions