Missing PlotArea

hatman

hatman

Well-known Member
Joined
Apr 8, 2005
Messages
2,664
I have the following code snippet:

Code: 
                Set Chrt = Charts.Add(before:=Sheets(Insert_Sheet_Name))
                
                Chrt.ChartType = xlXYScatterLinesNoMarkers
                
                'set the tab name and chart title
                
                Chrt.Name = shtCharts.Range("A" & Chrt_Row).Value
                
                Chrt.HasTitle = True

Which is part of a much larger application. Basically, this works fine almost always, but occassionally, when the code tries to set the HasTitle property, I get an error 'unable to set HasTitle Property'. When I debug, and look at the Chart, ther is NO PlotArea... and I can;t figure out why. Right now, I can;t figure my way out of it, I can;t figure out how to ensur ethat ther eis a PlotArea... or how to create one if it's missing. Any help woul dbe appreciated.
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
Sort by date Sort by votes
FWIW, Conan just did a joke about the ISS & the urine recycler not working. Jeepers, yer famous! :lol:

I was actually just getting ready to hit the hay. But I played a bit and we might need Peltier on this. Initially I pretty much just copied and pasted your code and it worked just fine. Then I tried to get all fancy and do some error checking. It worked just fine until I deliberately invoked an error and it stopped working. I've closed down Excel and I still can't get it to work again, even if I comment out the first block of code.
Code: 
Sub foo()
 
    Dim chtTest As Excel.Chart, shtChart As Object
    Set shtChart = Nothing
    On Error Resume Next
    Set shtChart = ThisWorkbook.Sheets(shtCharts.Range("A1").Text)
    If Not shtChart Is Nothing Then
        MsgBox "Chart already exists", vbExclamation, "friggin' nimrod"
        Exit Sub
    End If
    On Error GoTo 0
    Set chtTest = Charts.Add
 
    With chtTest
        .ChartType = xlXYScatterLinesNoMarkers
 
        '// set the tab name and chart title
        .Name = shtCharts.Range("A1").Value
        .HasTitle = True
    End With
End Sub

Which makes #@$% all sense to me and may indicate a bug or some other unique peculiarity. Jon's the best bet for having a friggin' clue. I'll drop him a PM to see if he can have a look.
 
Upvote 0
Does it help if you add data first?
 
Upvote 0
When using the Charts.Add the data region in relation to the active cell will be included in the chart.

If the active cell is in a empty region the chart will have no series and therefore can have no chart title.

You can use the Activechart.SeriesCollection.Count to check whether chart has any series when added.

By the same token you may want to remove additional series that you did not intend to chart.
 
Upvote 0
Greg Truby said:
...and we might need Peltier on this...
Click to expand...
Or Archibald or Pope. :biggrin:
rorya said:
Does it help if you add data first?
Click to expand...
Had it not been so late, I was going to try that however
Andy Pope said:
When using the Charts.Add the data region in relation to the active cell will be included in the chart.
Click to expand...
Andy has solved the mystery for me because I couldn't suss out why the code ran fine and later bombed. I would like to think that eventually it would have dawned on me that the active cell was the driver, but even on a good day, that's a dodgy bet. Late at night when I'm yawning, little chance I would have figured it out. So a tip of the cap to Mr. Pope. :bow:

And, of course, a thank you to Jon for looking - but by the time he drove by, our lads from across the pond had matters in hand.
 
Upvote 0
Excellent catch! You know, somewhere, I knew that relationship for manually adding a chart, but didn't do the mental extension to an automatic add. Makes sense, when you point it out. No problem, I just need to add a step to see if the activecell is blank, and if not, populate it with something, then delete it when I done (or select a cell that has something in it... depends on teh mood I;m in).

Thanx all (including Mr Peltier).
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Question Question
Can anyone help me get my VBA code working? I have spent tons of time but can't quite fix it.
Replies
3
Views
3K
profray
P
B
  • Question Question
Dynamic Ranges, creating multiple graphs, per productgroup / Macro - need experts
2
Replies
11
Views
3K
barowan
B
A
  • Question Question
Modify VBA code for different data structure
Replies
0
Views
555
artz
A

Forum statistics

Threads
1,222,675
Messages
6,167,534
Members
452,118
Latest member
djjamesp

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top