chart legen moves!

[ben.biddle@gmail.com]

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.

[ben.biddle@gmail.com]

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!

[Jon Peltier]

"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/
_______


<ben.biddle@gmail.com> wrote in message
news:1146157544.043899.327530@g10g2000cwb.googlegroups.com...
> 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!
>

[Jon Peltier]

You might find this somewhat easier:

http://peltiertech.com/Excel/Charts/ChartByControl.html

Also, for some introductory charting VBA:

http://peltiertech.com/Excel/ChartsH...kChartVBA.html

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

<ben.biddle@gmail.com> wrote in message
news:1146156415.577088.282310@u72g2000cwu.googlegroups.com...
> 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.
>

[ben.biddle@gmail.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?

Jon Peltier wrote:
> "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/
> _______
>
>
> <ben.biddle@gmail.com> wrote in message
> news:1146157544.043899.327530@g10g2000cwb.googlegroups.com...
> > 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!
> >

[Jon Peltier]

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/
_______

<ben.biddle@gmail.com> wrote in message
news:1146246698.791711.80040@g10g2000cwb.googlegroups.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?
>
> Jon Peltier wrote:
>> "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/
>> _______
>>
>>
>> <ben.biddle@gmail.com> wrote in message
>> news:1146157544.043899.327530@g10g2000cwb.googlegroups.com...
>> > 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!
>> >
>