chart legen moves!

  • Thread starter Thread starter ben.biddle
  • Start date Start date
B

ben.biddle

I've created a data set that is linked to a line graph (chart). I have
a macro, using a user form, that changes the values of the dataset, via
a function parameter, depending on what indicator the user wants to see
in the graph. The idea is to have a dynamic graph, something like the
functionality included in a pivotchart without requiring the user to
know how to use a pivotchart (I'm dumbing down my spreadsheet to be
accomodating). The problem I am running into is with formatting the
chart. The number of series in the chart can be between 1-8, but I've
set it up so that it will just display the blank series if the count is
fewer than 8. It is just simpler than trying to adjust the series
displayed every time the user changes the spreadsheet. What's
happening is every time I save the spread sheet, the legend moves,
slowing creeping toward the top of the chart area. If I set the
placement in the legend properties, the other parts of the chart area
resize to a less desirable configuration. Anyway I can stop this
legend creep or do I have to set the placement and deal with the
smaller plot area it creates? Also, if anyone knows some VBA code to
hide/show a series in the chart, that could help too!

Thanks.
 
I'm also having trouble changing the chart title using VBA. I've tried
recording a macro for it, but then I got an error when I ran the
recorded macro! One variation of the code I am trying to use is

With Sheet6.ChartObjects(chart)
.ChartTitle.Name = Cells(r1, 1).Value
End With

chart is a string variable with the name of the chart, in this case
chrt.Indicator1.

Thanks for your insight!
 
"Chart" is a keyword in VBA, so you should not be using it as a variable
name.

You can easily link a title to a cell without VBA. Select the title, type =
in the formula bar, and click on the cell.

If you must use VBA, use the macro recorder while you do something to get a
handle on the syntax. You need something that resembles this:

With Sheet6.ChartObjects(chart).Chart
.ChartTitle.Text = Sheet6.Cells(r1, 1).Value
End With

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______
 
Thanks Jon. That solves the minor issue of the title. As I wrote in
my posting, I did record a macro to get familiar with the syntax, but
even that code wouldn't work. It's a non-issue now that I know how to
link the title in the formula bar.

My original concern still remains. Is there a way to keep the legend
from moving aroudn without setting the placement? Or can I set the
placemetn and still adjust the size/shape of the legend?
 
In your macro you could reset the legend's position.

With ActiveChart.Legend
.Top = blah
.Left = blah
End With

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______
 
Back
Top