Default chart types in VB

[Weaver]

I wrote some code a while ago that runs a pivot table/pivot chart on a selected set of data, using

 charts.add

but this fails if the defaut chart type isn't compatible with the dataset. I'm trying to use a line graph, so is it possible to:

1 record the current defalut chart type
2 change it to what's required
3 draw the chart and
4 change the default back to what it was.

I found

expression.SetDefaultChart(FormatName)

in the help file but unless you're using a custom autoformat, the only other option is xlBuiltIn.

Any ideas?

 

[Weaver]

Okay, so I got part 2

Application.SetDefaultChart FormatName:=xlLineMarkers

(obvious, really...)

but since other users will be using my code and they might be quite happy with their current default chart, I need to be able to detect it and record the value somewhere, so I can change it back when done.

 

[RoryA]

If you select a blank cell first, you can just create the chart, change it to whatever type you want, then use SetSourceData to assign the pivot table's tablerange to the chart.

 

[Weaver]

If you select a blank cell first, you can just create the chart, change it to whatever type you want, then use SetSourceData to assign the pivot table's tablerange to the chart.

There's always more than one way to skin the proverbial cat. Thanks for that piece of lateral thinking!

 

[RoryA]

Sometimes we just need a way to stop Excel being 'helpful'.

 

[Weaver]

If anyone else reads this, you need to set the source to the datarange1 property of your pivot table, after setting the type to xlLine or similar.

For your blank cell, you can also use the cell where the page fields appear, so long as you haven't set any first, otherwise

 cells(rows.count,1).end(xlup).offset(1).select

will do

 

[RoryA]

I think you mean TableRange1 rather than Datarange1.

 

[Weaver]

I think you mean TableRange1 rather than Datarange1.

My bad. You are correct.