Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
chart legen moves!
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. |
#2
Posted to microsoft.public.excel.charting
|
|||
|
|||
chart legend moves!
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! |
#3
Posted to microsoft.public.excel.charting
|
|||
|
|||
chart legend moves!
"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/ _______ wrote in message oups.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! |
#4
Posted to microsoft.public.excel.charting
|
|||
|
|||
chart legen moves!
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/ _______ wrote in message oups.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. |
#5
Posted to microsoft.public.excel.charting
|
|||
|
|||
chart legend moves!
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/ _______ wrote in message oups.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! |
#6
Posted to microsoft.public.excel.charting
|
|||
|
|||
chart legend moves!
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/ _______ wrote in message ups.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/ _______ wrote in message oups.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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Activating a Chart object | Charts and Charting in Excel | |||
Urgent Chart Assistance | Charts and Charting in Excel | |||
Urgent Chart Questions | Excel Discussion (Misc queries) | |||
Urgent Chart Assistance Requested | Excel Discussion (Misc queries) | |||
Scrollbar on Chart Jumps to Left when Chart is Clicked | Charts and Charting in Excel |