Chart Legend Items: hide/show
How do I hide/show a series on a chart legend (scatter plot.)
I tried a null string (""), but the trace still shows. I also tried #N/A. But it then shows "#N/A"; the same with function: NA(). What I am trying to do is to have a control button to show or hide a series from the chart. I can set all the series values to #N/A which hides it, but I am having trouble trying to hide the legend for the series. I also tried an "if" function in the chart-series dialog, but It evidently will only accept a cell pointer. |
You can manually hide a legend entry by selecting it (select the legend
first, then the entry, using two single clicks) and pressing Delete. To get it back you have to delete the entire legend and then add it back. You can't automatically adjust the legend based on a formula. You can filter the data, and any hidden data isn't plotted, either as a series or as a legend entry. But this isn't automatic either. You could write a macro which hides and restores the legend based on the visibility of the series. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Richard Ahlvin wrote: How do I hide/show a series on a chart legend (scatter plot.) I tried a null string (""), but the trace still shows. I also tried #N/A. But it then shows "#N/A"; the same with function: NA(). What I am trying to do is to have a control button to show or hide a series from the chart. I can set all the series values to #N/A which hides it, but I am having trouble trying to hide the legend for the series. I also tried an "if" function in the chart-series dialog, but It evidently will only accept a cell pointer. |
"Jon Peltier" wrote in message ... You can manually hide a legend entry by selecting it (select the legend first, then the entry, using two single clicks) and pressing Delete. To get it back you have to delete the entire legend and then add it back. I have done this for the series I want to always appear. (I have data points series with legend entrys and an associated line series with no legend entry; I dynamically change these A-OK.) You can't automatically adjust the legend based on a formula. You can filter the data, and any hidden data isn't plotted, either as a series or as a legend entry. But this isn't automatic either. This is a viable approach; I have x-y (scatter) plots of some series. I have been setting the y's to #N/A which makes the series disappear on the plot but not its legend entry. Also I can make the legend text disappear by setting its cell null (i.e. "") but that does not remove the entry, it just removes the text. You could write a macro which hides and restores the legend based on the visibility of the series. This is an approach I could try; But I have not found the chart object; it appears to be a script and not a VBA object. I think I could figgure it out in VBA, but I'm not familiar with the scripting (which I thought was obsolete!) I have been able to copy a complicated chart and change its source data by making the chart a separate sheet, going to the script, then using find/replace to change the data sheet name, then move the chart back to the desired location. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Richard Ahlvin wrote: How do I hide/show a series on a chart legend (scatter plot.) I tried a null string (""), but the trace still shows. I also tried #N/A. But it then shows "#N/A"; the same with function: NA(). What I am trying to do is to have a control button to show or hide a series from the chart. I can set all the series values to #N/A which hides it, but I am having trouble trying to hide the legend for the series. I also tried an "if" function in the chart-series dialog, but It evidently will only accept a cell pointer. |
Richard Ahlvin wrote: You could write a macro which hides and restores the legend based on the visibility of the series. This is an approach I could try; But I have not found the chart object; it appears to be a script and not a VBA object. I think I could figgure it out in VBA, but I'm not familiar with the scripting (which I thought was obsolete!) I have been able to copy a complicated chart and change its source data by making the chart a separate sheet, going to the script, then using find/replace to change the data sheet name, then move the chart back to the desired location. Richard - What do you mean by "scripting"? Are you referring to the SERIES formula in the formula bar, when the series is selected? - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ |
I go to: Tools-Macro-Microsoft Script Editor
I see a window with a list of the various sheets on the right. The main window appears to have something that looks like HTML or maybe XML. I assume this is a script that is creating the chart. I have successfully edited that text to change the reference sheet name. (My design is a worksheet with all the data, text, legends, control references, etc. on it, and another sheet containing the actual chart and the control graphics.) To make another chart, I copy the chart to a new sheet, so I now have 2 charts but referencing the same data. I make up another sheet with the new data and then go into this script editor and change all of the sheet names from the first to the new sheet. This has successfully created a second chart using data from the new sheet. I don't know any other way to do this quickly. The other method I know is to access each series on the new chart (which is still referencing the old data sheet) and re-do the label, x-data, and the y-data for maybe 10 or 12 series; this is very laborious and time consuming. If I go to the VBA editor: Tools-Macro-Visual Basic Editor I see the sheet objects, but none of them have any code, and I don't see the chart objects...(Perhaps I just don't know where to look.) I would like to "go-in" and change the code/script/whatever to manipulate the legend slightly differently than is being done by default. "Jon Peltier" wrote in message ... Richard Ahlvin wrote: You could write a macro which hides and restores the legend based on the visibility of the series. This is an approach I could try; But I have not found the chart object; it appears to be a script and not a VBA object. I think I could figgure it out in VBA, but I'm not familiar with the scripting (which I thought was obsolete!) I have been able to copy a complicated chart and change its source data by making the chart a separate sheet, going to the script, then using find/replace to change the data sheet name, then move the chart back to the desired location. Richard - What do you mean by "scripting"? Are you referring to the SERIES formula in the formula bar, when the series is selected? - Jon |
Richard -
Very interesting. You've discovered the XML code for the worksheet and its embedded charts. I haven't considered editing this script to change charts, but now I'm going to have to play with it. I've written a simple utility to change parts of the series formula (such as address strings or sheet names): http://peltiertech.com/Excel/Charts/ChgSrsFmla.html This might help you to change the sheet names. You can change all formulas in the active chart, or in all charts on the active sheet. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Richard Ahlvin wrote: I go to: Tools-Macro-Microsoft Script Editor I see a window with a list of the various sheets on the right. The main window appears to have something that looks like HTML or maybe XML. I assume this is a script that is creating the chart. I have successfully edited that text to change the reference sheet name. (My design is a worksheet with all the data, text, legends, control references, etc. on it, and another sheet containing the actual chart and the control graphics.) To make another chart, I copy the chart to a new sheet, so I now have 2 charts but referencing the same data. I make up another sheet with the new data and then go into this script editor and change all of the sheet names from the first to the new sheet. This has successfully created a second chart using data from the new sheet. I don't know any other way to do this quickly. The other method I know is to access each series on the new chart (which is still referencing the old data sheet) and re-do the label, x-data, and the y-data for maybe 10 or 12 series; this is very laborious and time consuming. If I go to the VBA editor: Tools-Macro-Visual Basic Editor I see the sheet objects, but none of them have any code, and I don't see the chart objects...(Perhaps I just don't know where to look.) I would like to "go-in" and change the code/script/whatever to manipulate the legend slightly differently than is being done by default. "Jon Peltier" wrote in message ... Richard Ahlvin wrote: You could write a macro which hides and restores the legend based on the visibility of the series. This is an approach I could try; But I have not found the chart object; it appears to be a script and not a VBA object. I think I could figgure it out in VBA, but I'm not familiar with the scripting (which I thought was obsolete!) I have been able to copy a complicated chart and change its source data by making the chart a separate sheet, going to the script, then using find/replace to change the data sheet name, then move the chart back to the desired location. Richard - What do you mean by "scripting"? Are you referring to the SERIES formula in the formula bar, when the series is selected? - Jon |
All times are GMT +1. The time now is 01:31 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com