Removing unnamed Legend lines from an Excel template

I have created an Excel template with a maximum number of 10 series.
The worksheet is populated with series data from an Access application.

Everything works fine in terms of getting the data from Access to Excel
and then having the chart created.

The problem is that if 9 or fewer series are sent to the template, the
Legend still shows all ten series even though the chart shows only the
actual number of series.  The "extra" series shown in the Legend do not
have a name.

I want to remove these 'extra' series from the Legend.  I think it
would be easier to do this within the Excel template than to try to do
this from MS Access.  In the Chart_Activate procedure

I tried looking for  .SeriesCollection(i).Name = "" or
IsNull(.SeriesCollection(i).Name), but errors are generated in both
cases.

Then I thought, why not just trap the error and delete the unnamed
Legend item in the error handler using:
ActiveChart.Legend.LegendEntries(i).Delete

This actually worked, UNTIL, I activated the chart a second time.  Then
I started getting an error because apparantly the series count actually
is ten even though only the number of series actually sent from Access
are charted.

Any thoughts on how I can reset the legend in the Excel template to
only display the actual number of series that are being plotted (even
though the template is set up to display ten).

Thanks.

0
rdemyan (4)
1/22/2007 3:57:06 PM
excel.charting 18370 articles. 0 followers. Follow

4 Replies
461 Views

Similar Articles

[PageSpeed] 23

Shane:

Thanks for the advice.  I prefer VBA to macros, but have never done
this before in Excel.  Do you have any examples you could point me to?


ShaneDevenshire wrote:
> Hi,
>
> I think the best solution is to create a macro that does what the template
> does, except that it is run each time and will handle the changing data very
> easily.
>
> --
> Cheers,
> Shane Devenshire
>
>
> "rdemyan" wrote:
>
> > I have created an Excel template with a maximum number of 10 series.
> > The worksheet is populated with series data from an Access application.
> >
> > Everything works fine in terms of getting the data from Access to Excel
> > and then having the chart created.
> >
> > The problem is that if 9 or fewer series are sent to the template, the
> > Legend still shows all ten series even though the chart shows only the
> > actual number of series.  The "extra" series shown in the Legend do not
> > have a name.
> >
> > I want to remove these 'extra' series from the Legend.  I think it
> > would be easier to do this within the Excel template than to try to do
> > this from MS Access.  In the Chart_Activate procedure
> >
> > I tried looking for  .SeriesCollection(i).Name = "" or
> > IsNull(.SeriesCollection(i).Name), but errors are generated in both
> > cases.
> >
> > Then I thought, why not just trap the error and delete the unnamed
> > Legend item in the error handler using:
> > ActiveChart.Legend.LegendEntries(i).Delete
> >
> > This actually worked, UNTIL, I activated the chart a second time.  Then
> > I started getting an error because apparantly the series count actually
> > is ten even though only the number of series actually sent from Access
> > are charted.
> >
> > Any thoughts on how I can reset the legend in the Excel template to
> > only display the actual number of series that are being plotted (even
> > though the template is set up to display ten).
> > 
> > Thanks.
> > 
> >

0
rdemyan (4)
1/23/2007 6:20:40 AM
Try going to http://peltiertech.com/Excel/Charts/ChartIndex.html

And looking under the topic:
VBA Charting Techniques and Examples


-- 
Cheers,
Shane Devenshire


"rdemyan" wrote:

> Shane:
> 
> Thanks for the advice.  I prefer VBA to macros, but have never done
> this before in Excel.  Do you have any examples you could point me to?
> 
> 
> ShaneDevenshire wrote:
> > Hi,
> >
> > I think the best solution is to create a macro that does what the template
> > does, except that it is run each time and will handle the changing data very
> > easily.
> >
> > --
> > Cheers,
> > Shane Devenshire
> >
> >
> > "rdemyan" wrote:
> >
> > > I have created an Excel template with a maximum number of 10 series.
> > > The worksheet is populated with series data from an Access application.
> > >
> > > Everything works fine in terms of getting the data from Access to Excel
> > > and then having the chart created.
> > >
> > > The problem is that if 9 or fewer series are sent to the template, the
> > > Legend still shows all ten series even though the chart shows only the
> > > actual number of series.  The "extra" series shown in the Legend do not
> > > have a name.
> > >
> > > I want to remove these 'extra' series from the Legend.  I think it
> > > would be easier to do this within the Excel template than to try to do
> > > this from MS Access.  In the Chart_Activate procedure
> > >
> > > I tried looking for  .SeriesCollection(i).Name = "" or
> > > IsNull(.SeriesCollection(i).Name), but errors are generated in both
> > > cases.
> > >
> > > Then I thought, why not just trap the error and delete the unnamed
> > > Legend item in the error handler using:
> > > ActiveChart.Legend.LegendEntries(i).Delete
> > >
> > > This actually worked, UNTIL, I activated the chart a second time.  Then
> > > I started getting an error because apparantly the series count actually
> > > is ten even though only the number of series actually sent from Access
> > > are charted.
> > >
> > > Any thoughts on how I can reset the legend in the Excel template to
> > > only display the actual number of series that are being plotted (even
> > > though the template is set up to display ten).
> > > 
> > > Thanks.
> > > 
> > >
> 
> 
0
1/23/2007 7:26:00 AM
> I prefer VBA to macros

Excel macros are written in VBA.

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


"rdemyan" <rdemyan@hotmail.com> wrote in message 
news:1169533240.328922.303020@11g2000cwr.googlegroups.com...
> Shane:
>
> Thanks for the advice.  I prefer VBA to macros, but have never done
> this before in Excel.  Do you have any examples you could point me to?
>
>
> ShaneDevenshire wrote:
>> Hi,
>>
>> I think the best solution is to create a macro that does what the 
>> template
>> does, except that it is run each time and will handle the changing data 
>> very
>> easily.
>>
>> --
>> Cheers,
>> Shane Devenshire
>>
>>
>> "rdemyan" wrote:
>>
>> > I have created an Excel template with a maximum number of 10 series.
>> > The worksheet is populated with series data from an Access application.
>> >
>> > Everything works fine in terms of getting the data from Access to Excel
>> > and then having the chart created.
>> >
>> > The problem is that if 9 or fewer series are sent to the template, the
>> > Legend still shows all ten series even though the chart shows only the
>> > actual number of series.  The "extra" series shown in the Legend do not
>> > have a name.
>> >
>> > I want to remove these 'extra' series from the Legend.  I think it
>> > would be easier to do this within the Excel template than to try to do
>> > this from MS Access.  In the Chart_Activate procedure
>> >
>> > I tried looking for  .SeriesCollection(i).Name = "" or
>> > IsNull(.SeriesCollection(i).Name), but errors are generated in both
>> > cases.
>> >
>> > Then I thought, why not just trap the error and delete the unnamed
>> > Legend item in the error handler using:
>> > ActiveChart.Legend.LegendEntries(i).Delete
>> >
>> > This actually worked, UNTIL, I activated the chart a second time.  Then
>> > I started getting an error because apparantly the series count actually
>> > is ten even though only the number of series actually sent from Access
>> > are charted.
>> >
>> > Any thoughts on how I can reset the legend in the Excel template to
>> > only display the actual number of series that are being plotted (even
>> > though the template is set up to display ten).
>> >
>> > Thanks.
>> >
>> >
> 


0
jonxlmvpNO (4558)
1/23/2007 1:01:22 PM
Thank you for pointing me to Jon's website.  There appears to be a lot
of good information there which I'm sure will help me in many areas.

However, in doing more research on my issue, it appears that the
problem has to do with <blank series>.  When looking under Source Data,
Series tab, there are multiple entries entitled <blank series>,  I bet
if I can delete these, the corresponding legend items will go away.

Further research seems to indicate that these series are not easy to
delete using VBA.  In cycling through the SeriesCollection, I can't
even figure out how to identify which series is a <blank series>.

Still, it would seem that if I can just delete (remove) these <blank
series>, that this would be the easiest solution to my problem.

Thanks.


ShaneDevenshire wrote:
> Try going to http://peltiertech.com/Excel/Charts/ChartIndex.html
>
> And looking under the topic:
> VBA Charting Techniques and Examples
>
>
> --
> Cheers,
> Shane Devenshire
>
>
> "rdemyan" wrote:
>
> > Shane:
> >
> > Thanks for the advice.  I prefer VBA to macros, but have never done
> > this before in Excel.  Do you have any examples you could point me to?
> >
> >
> > ShaneDevenshire wrote:
> > > Hi,
> > >
> > > I think the best solution is to create a macro that does what the template
> > > does, except that it is run each time and will handle the changing data very
> > > easily.
> > >
> > > --
> > > Cheers,
> > > Shane Devenshire
> > >
> > >
> > > "rdemyan" wrote:
> > >
> > > > I have created an Excel template with a maximum number of 10 series.
> > > > The worksheet is populated with series data from an Access application.
> > > >
> > > > Everything works fine in terms of getting the data from Access to Excel
> > > > and then having the chart created.
> > > >
> > > > The problem is that if 9 or fewer series are sent to the template, the
> > > > Legend still shows all ten series even though the chart shows only the
> > > > actual number of series.  The "extra" series shown in the Legend do not
> > > > have a name.
> > > >
> > > > I want to remove these 'extra' series from the Legend.  I think it
> > > > would be easier to do this within the Excel template than to try to do
> > > > this from MS Access.  In the Chart_Activate procedure
> > > >
> > > > I tried looking for  .SeriesCollection(i).Name = "" or
> > > > IsNull(.SeriesCollection(i).Name), but errors are generated in both
> > > > cases.
> > > >
> > > > Then I thought, why not just trap the error and delete the unnamed
> > > > Legend item in the error handler using:
> > > > ActiveChart.Legend.LegendEntries(i).Delete
> > > >
> > > > This actually worked, UNTIL, I activated the chart a second time.  Then
> > > > I started getting an error because apparantly the series count actually
> > > > is ten even though only the number of series actually sent from Access
> > > > are charted.
> > > >
> > > > Any thoughts on how I can reset the legend in the Excel template to
> > > > only display the actual number of series that are being plotted (even
> > > > though the template is set up to display ten).
> > > > 
> > > > Thanks.
> > > > 
> > > >
> > 
> >

0
rdemyan (4)
1/23/2007 1:56:31 PM
Reply:

Similar Artilces:

Excel on website
Hi, I have an Excel file which opens maximum at my desk, but it opens minimum when I view it on my company's intranet. I use FrontPage 03 and Excel 07. Please advice! Thanks Chi After moving the file to server and linked it to the page I got it! Thanks Chi "Chi" wrote: > Hi, > > I have an Excel file which opens maximum at my desk, but it opens minimum > when I view it on my company's intranet. I use FrontPage 03 and Excel 07. > > Please advice! > > Thanks > Chi > > > > > > Aft...

Sum
Hi, I just found this "feature" at Excel formulas today. Please, have a look at: http://danielgudang.multiply.com/journal/item/192 (in portuguese, but images show all) Let me explain: some cell C1 = sum (C2:C5) some cell D1 = sum (D2:D5) some cell E1 = sum (E2:E5) A1 = C1 - D1 - E1 B1 = C1 - (D1 + E1) A1 = B1 ??? Oh, not always! Sometimes A1 will be +0, sometimes -0. Really strange! It's a feature of any application that uses IEEE double precision floating point math (e.g., every commercial spreadsheet I know of). It's the result of having finite precision ...

Removing Exchange 2000
I have a server that was a DC. I have demoted it to a member server and now I want to Uninstall Exchange 2000. However when I do that I get the message that it cannot contact the Schema Master. My new DC is the Schema Master so I do not know how to get around this. Note: I am logged in as the Domain and Enterprise Admin. THanx http://support.microsoft.com/default.aspx?scid=kb;en-us;260378&sd=tech http://support.microsoft.com/default.aspx?scid=kb;en-us;q273478 On Tue, 22 Mar 2005 15:34:44 -0500, "Gerald Ray" <geraldr@pclaw.com> wrote: >I have a server that...

Create Template
Hi i am new with Outlook How can i crate a Template so that when i send mail it should go my Co. logo and address evey time to other. pls help. Shital In news:13072DF7-B1A1-4ED3-8E94-4128335F3992@microsoft.com, shital shah <shitalshah@discussions.microsoft.com> typed: > Hi i am new with Outlook > How can i crate a Template so that when i send mail it should go my > Co. logo and address evey time to other. > > pls help. > > Shital I don't understand. Are you saying you want an e-mail signature automatically put at the bottom of your messages? Please provi...

Money 2002
Can missing Form 140 Schedule C line names be added? If so, how? ...

Refreshing ODBC link in Template
I have a template and the link to the ODBC connection needs to be updated. There was a slight change to the table, so I need to refresh the link. This is something I can easily do in Access, but I don't see where to do this in my template. Can someone please point me in the right direction? Thanks in Advance, Trisha ...

Embedded Excel worksheet in PowerPoint
I'm working with an embedded excel worksheet in powerponit. The doc is sent to me and if I have to edit the worksheet, I find that some cells have missing characters once I open the worksheet to edit it. It appears that characters beyond some length (256?) are being truncated off. How do I resolve this so I don't have this problem again? Thanks, Barb Reinhardt This kind of problem may occur if the material was inserted into the embedded worksheet via copy/paste from an external sheet. A reliable approach is to open the embedded sheet first, and from that sheet do: File ...

Excel 2003 01-27-10
Hi all, hopefully you can help me. In cell B21 the user will always input an email address, i have created a button and now need the button to automatically email the email address in cell B21. I then need it to default and save to a specific location with the file name automatically generated from b12. Please help. :) ...

Chart Legend #3
I have Line charts on various sheets. The charts are identical in size. There are 12 items graphed on each chart. However, the legend on each of the charts seems to be displaying different numbers of charted items. I'm not sure how to explain this clearly. On one chart's legend, it might show 12 items (three columns with four items each), which is correct. Another chart's legend may only show 2 columns totally only eight items (it should be 12). I even have one chart's legend showing only 4 items (as if it is only one column displaying). I can't figure out what...

Purge data based on another Excel file???
I am attempting to purge data from a large list based on data in another document. I have the files I want to remaove in on file and the file I want to remove them from. There should be simple way to do this no? ...

removing black lines
Sometimes I type an underline bar across the page. At the end of the page I'll hit the return (enter) key. When I do, the underline bar turns into a big black bar. Then I can't get rid of it. It just stays there. How can I get rid of this black bar? See http://word.mvps.org/FAQs/Formatting/CantGetRidOfLine.htm -- <>>< ><<> ><<> <>>< ><<> <>>< <>><<> Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org <>>< ><<> &...

Missing grid lines
I have a standard worksheet that covers the array of cells A4 to Q70 that originally included grid lines. Unfortunately the grid lines have vanished from the segment covered by cells A14 to E65. Using Tools/Options/View to check or uncheck the Gridlines box turns the gridlines on or off but does not restore the gridlines in the bare segment. I have tried everything I could think of to get rid of this annoying situation including copying and pasting to a new worksheet. Nothing I have tried works. I would be grateful if someone would help me solve this problem Hi sounds like the cel...

Removing the (1) figure before phone numbers
How can I delete the (1) figure in front of some of my phone numbers? -- phildee Delete it. Was there more to your question that you did not post? -- Russ Valentine "Phil Dee" <PhilDee@discussions.microsoft.com> wrote in message news:299FB137-E06E-43A8-825F-498EF923BB30@microsoft.com... > How can I delete the (1) figure in front of some of my phone numbers? > -- > phildee ...

Removing Campaign Templates and Template Activities from default v
I am trying to figure out how to prevent template activities and campaign templates from showing up in default views in the "Activities" section of CRM as well as the "Advanced Find" and "Regarding" views. ...

EXCEL 2007
I have a GIS application which uses .dbf files (not sure if they are actually Dbase files). When I need to append data (fields) to the files, I have a big problem in that excel 2007 no longer allows save as / export to .dbf files. If I try bring the data in MS Access and save as .dbf files, I have untold problems with the GIS application... The only way I have been successful with this is to find a user with an older version of excel, insert the data as database columns and then save as .dbf Any suggestions as to how I can get around this with out purchasing a converter? ...

in Excel: How do I change "selected cells" highlight color?
When I select cells in Excel the shading is so light (blue) that I can barely tell which cells are selected from those that are not. Can I change the background color of cells that are selected? Thanks. Assuming Excel 2007 this is a known problem and as yet no patch for it. I don't have Excel 2007 but I have read that playing with your Contrast can help some. Gord Dibben MS Excel MVP On Tue, 10 Jun 2008 12:51:00 -0700, flameretired <flameretired@discussions.microsoft.com> wrote: >When I select cells in Excel the shading is so light (blue) that I can barely >tell whic...

email from excel workbook button does nothing
In my old version of excel(2003) as long as i had outlook open i could email directly from the worksheet. Now i have office 2007 with "windows mail" when i press the button in the "quick access" tool bar it does nothing. I have tried having widows mail open when I do it too but the only way i can send is by making file attachments. seems like the long way around. Is there something I am doing wrong? I have tried to read some of the previous posts and dont understand anything about codes ect. Please help, Mark hi Mark Is Windows Mail your default m...

connecting cells with connector lines
Hello! Is it possible in Excel (2007) to draw a connector line (with or without an arrow ...) between two cells in a worksheet, so that the line-tips will follow their cells even when the latter are moved ? Thanks Michael On Feb 25, 7:00=A0am, Michaelprem123 <michaelprem...@gmail.com> wrote: > Hello! > > Is it possible in Excel (2007) to draw a connector line (with or > without an arrow ...) between two cells in a worksheet, so that the > line-tips will follow their cells even when the latter are moved ? > > Thanks > > Michael The line will automatically a...

Delete duplicate rows from a list in Excel
Hi, Below are instructions on how to "delete duplicate rows from a list in Excel". I learned about this tip from the Microsoft Office Assistant website. However, each time I try step 4, my list is not filtered and no records are hidden. Is there a secret I am missing to make this work? Thanks for your help! -Greg http://office.microsoft.com/en-us/assistance/HA010346261033.aspx Applies to Microsoft Office Excel 2003 Microsoft Excel 2002 A duplicate row (also called a record) in a list is one where all values in the row are an exact match of all the values in another row. To delet...

How to tell Excel to insert cells and shift down from Access
Hi all, I have Access 2003. I am trying to tell Excel to insert cells and shift cells down from Access, with this line of code: oWksh.Cells.Insert Shift:=xlDown But Access does not like that line. It gave me run-time error 1004. I know if has to do with the part Shift:=xlDown Can you suggestion what I can do? Thank you in advance, Ben "Ben" <Ben@NoSpam.com> wrote in message news:eSZuzcDpKHA.1892@TK2MSFTNGP02.phx.gbl... > Hi all, > > I have Access 2003. I am trying to tell Excel to insert cells and shift > cells down from Access,...

Invoice template
While working with the Excel Invoice template, I saw, at one time, an Invoice tool bar. I figured this would show up whenever I used the Invoice spreadsheet. But - now that I have made changes to it, I don't see it anymore. Does anyone know how to get the Invoice tool bar to show up? The Workbook_Open macro shown below will increment the number in cell A1 of the sheet named "MySheet" whenever the workbook is opened. A word of caution: If you're like me you sometimes open the file with the intention of preparing a Purchase Order but get side-tracked for one reason ...

Text Wrap in Excel 2002
I have been having difficultly wrapping text. I merge several cells and wrap text sucessfully. However, if I go into one of the cells and add a couple of sentences and enter, my text that was wrapped perfectly is simply a string of #########'s and I can't view the text correctly by changing the row height or reformatting (even though it is all still there - as I can see in the edit box - when my cursor is on the text box). Any suggestions? Try formatting the cell as General. bdean wrote: > > I have been having difficultly wrapping text. I merge > several cells an...

Excel Version When Posting Please State Your Excel Version!!!
On Jan 8, 6:58=A0pm, "Tyro" <T...@hotmail.com> wrote: > Subject: Excel Version When Posting Please State Your > Excel Version!!! And Please Include Your Questions and Comments In the Body of Your Posting!!! Not just in the subject line, even if it simply duplicates the subject line. Some news readers truncate the subject line. Some news servers do not permit you to reply to articles with empty bodies. Some news servers seem to reject such articles when they come in from the network, or perhaps the originating news server does not push them into the network. That ...

EXCEL Cell Formatting: Custom or Conditional
At work I have Excel 2002 and I have some cells that I import data in from the mainframe. I have a table where there is a column for GENDER (MALE or FEMALE) that I would like to see shown as "Male" or "Female" (mixed case). Is there a way to change all upper case to mixed case using either CUSTOM formatting or CONDITIONAL formatting? I would prefer not using a macro if there is something simpler. I know that =PROPER() produces the correct result, but I don't see how I can use this unless I'm referencing another cell. I would like to be able to just change what fa...

Excel Formulas
I have a spreadsheet showing whether customers have paid their debts or not. Each row represents a customer and details of the debt and the last cell in the row states whether the debt is "PAID" OR "UNPAID". Where the word "PAID" appears in a cell, I want to write a formula that will move that whole row to a different location on my sheet. I eventually want to record this action on a MACRO, so that every time I run a MACRO on my accounts spreadsheet it will take the updated information of customers marked "paid" and put in a seperate spreadsheet. Ple...