How do I find the series associated with a given legend entry.

I have a problem, whereby I need to find the association between a legend 
entry and a chart series (in order that I can do something with the legend 
entry for a specific series if it exists). 

I cannot find a way to establish a firm link between a series and its 
legend, for instance:

The chart has 5 series.
The legend had 5 series but the user (or something/somebody) deleted 2 of 
these legend entries so there are 3 legend entries. 

So, at this point lets say I have code which has a specific series in its 
hand:
Excel.Series series = _chart.Series(3);

And with this series I want to delete the legend entry:
Excel.LegendEntry entry = _chart.Legend.LegendEntries( <entryForSeries3> )

Is there something I am missing here? Or is there a method on an object that 
I haven't seen that allows me to get the LegendEntry associated with a series?
0
SamW1 (8)
8/17/2009 11:32:01 AM
excel.charting 18370 articles. 0 followers. Follow

4 Replies
425 Views

Similar Articles

[PageSpeed] 27

We can hide a specific series by controlling the PlotOrder. If we make the 
target series plotOrder = 1, then we can delete legend entry 1. Then, restore 
plot order to what it was before. Note that this macro assumes chart is 
already activated, but I presume you can figure out how to code that part.

Sadly, it also makes the rather large assumption that the legend entry has 
not already been deleted. But hopefully this gives you a start.

Sub HideName()
Dim NSrs As Integer
Dim CurrentOrder As Integer

'Name of series to hide legend for
xName = "MySeries"

    With ActiveChart
    NSrs = .SeriesCollection.Count
    For i = 1 To NSrs
    If .SeriesCollection(i).Name = "Girl" Then
    CurrentOrder = .SeriesCollection(i).PlotOrder
    .SeriesCollection(i).PlotOrder = 1
    .Legend.LegendEntries(1).Delete
    .SeriesCollection(i).PlotOrder = CurrentOrder
    End If
    Next
    End With
End Sub
-- 
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"SamW" wrote:

> 
> I have a problem, whereby I need to find the association between a legend 
> entry and a chart series (in order that I can do something with the legend 
> entry for a specific series if it exists). 
> 
> I cannot find a way to establish a firm link between a series and its 
> legend, for instance:
> 
> The chart has 5 series.
> The legend had 5 series but the user (or something/somebody) deleted 2 of 
> these legend entries so there are 3 legend entries. 
> 
> So, at this point lets say I have code which has a specific series in its 
> hand:
> Excel.Series series = _chart.Series(3);
> 
> And with this series I want to delete the legend entry:
> Excel.LegendEntry entry = _chart.Legend.LegendEntries( <entryForSeries3> )
> 
> Is there something I am missing here? Or is there a method on an object that 
> I haven't seen that allows me to get the LegendEntry associated with a series?
0
LukeM (202)
8/17/2009 2:50:01 PM
If you delete the current legend and then add a new one, the new legend 
should show all five series.  At that point you could link the series and 
legend by setting up a variable that you would use for both you series and 
legend entries i.e. something like this:

Sub Test()

    Dim Cht As Chart
    Dim SrsNbr As Integer

    Set Cht = ActiveChart

    SrsNbr = 3

    Cht.SeriesCollection(SrsNbr).Select
    Cht.Legend.LegendEntries(SrsNbr).Select

End Sub

Unfortunately this will only work if all legend entries are included (none 
have been deleted).  There's doesn't seem to be an object or method allowing 
the  functionality you need that I can find.  So, if a legend entry is 
deleted you would have to somehow keep track of the new order in your code. 

-- 
John Mansfield
http://www.cellmatrix.net


"SamW" wrote:

> 
> I have a problem, whereby I need to find the association between a legend 
> entry and a chart series (in order that I can do something with the legend 
> entry for a specific series if it exists). 
> 
> I cannot find a way to establish a firm link between a series and its 
> legend, for instance:
> 
> The chart has 5 series.
> The legend had 5 series but the user (or something/somebody) deleted 2 of 
> these legend entries so there are 3 legend entries. 
> 
> So, at this point lets say I have code which has a specific series in its 
> hand:
> Excel.Series series = _chart.Series(3);
> 
> And with this series I want to delete the legend entry:
> Excel.LegendEntry entry = _chart.Legend.LegendEntries( <entryForSeries3> )
> 
> Is there something I am missing here? Or is there a method on an object that 
> I haven't seen that allows me to get the LegendEntry associated with a series?
0
8/18/2009 12:15:01 PM
In summary I guess that both of your post's answered the question, there just 
isn't  a sure-fire way to work out what legend entry ties to what series. 

I came up with a fairly horrible other way which meets my needs (but it 
certainly horrific). 

In essence what I ended up doing is using the 
LegendKey.LegendEntry.Format.Fill.BackColor and matching that with the 
Series.Format.Fill.BackColor. 

There's a few different ways that this approach could go wrong, its not 
advisable (for instance not all series have fill colors and in those cases 
you would have to use the Marker colors or something instead for lines), it 
happened to work in my case. 

Some kind of more complex matching method between the LegendKey and the 
series would be possible given time if anybody else gets stuck with this. 

Thanks
Sam

"SamW" wrote:

> 
> I have a problem, whereby I need to find the association between a legend 
> entry and a chart series (in order that I can do something with the legend 
> entry for a specific series if it exists). 
> 
> I cannot find a way to establish a firm link between a series and its 
> legend, for instance:
> 
> The chart has 5 series.
> The legend had 5 series but the user (or something/somebody) deleted 2 of 
> these legend entries so there are 3 legend entries. 
> 
> So, at this point lets say I have code which has a specific series in its 
> hand:
> Excel.Series series = _chart.Series(3);
> 
> And with this series I want to delete the legend entry:
> Excel.LegendEntry entry = _chart.Legend.LegendEntries( <entryForSeries3> )
> 
> Is there something I am missing here? Or is there a method on an object that 
> I haven't seen that allows me to get the LegendEntry associated with a series?
0
SamW1 (8)
8/18/2009 2:49:01 PM
You're right, there's no reliable way in the object model to match up 
series with legend entries.

But you could extend your approach further. You might have multiple 
series with the same formatting, which complicates things, or you might 
have points in the series which have unique formatting, which 
complicates things more.

I won't write the code, but I'll outline for you how I would do this.


Function WhichLegendEntry(WhichSeries) as long

   WhichLegendEntry=0 ' default value, returned if no match

   get formatting of series WhichSeries
     if you suspect points are differently formatted, get series formats,
     then make an array for the points, and if a point's formatting
     differs from the series formatting, store it in the array

   Apply some far out format to the series

   find the legend entry that has the same wild format
     Return this from the function
     Return zero if no match

   Reapply original series formatting
     check array for points with unique formatting,
     reapply as applicable.

End Function


- Jon
-------
Jon Peltier
Peltier Technical Services, Inc.
http://peltiertech.com/



SamW wrote:
> In summary I guess that both of your post's answered the question, there just 
> isn't  a sure-fire way to work out what legend entry ties to what series. 
> 
> I came up with a fairly horrible other way which meets my needs (but it 
> certainly horrific). 
> 
> In essence what I ended up doing is using the 
> LegendKey.LegendEntry.Format.Fill.BackColor and matching that with the 
> Series.Format.Fill.BackColor. 
> 
> There's a few different ways that this approach could go wrong, its not 
> advisable (for instance not all series have fill colors and in those cases 
> you would have to use the Marker colors or something instead for lines), it 
> happened to work in my case. 
> 
> Some kind of more complex matching method between the LegendKey and the 
> series would be possible given time if anybody else gets stuck with this. 
> 
> Thanks
> Sam
> 
> "SamW" wrote:
> 
>> I have a problem, whereby I need to find the association between a legend 
>> entry and a chart series (in order that I can do something with the legend 
>> entry for a specific series if it exists). 
>>
>> I cannot find a way to establish a firm link between a series and its 
>> legend, for instance:
>>
>> The chart has 5 series.
>> The legend had 5 series but the user (or something/somebody) deleted 2 of 
>> these legend entries so there are 3 legend entries. 
>>
>> So, at this point lets say I have code which has a specific series in its 
>> hand:
>> Excel.Series series = _chart.Series(3);
>>
>> And with this series I want to delete the legend entry:
>> Excel.LegendEntry entry = _chart.Legend.LegendEntries( <entryForSeries3> )
>>
>> Is there something I am missing here? Or is there a method on an object that 
>> I haven't seen that allows me to get the LegendEntry associated with a series?
0
jonNO1 (306)
8/18/2009 5:28:30 PM
Reply:

Similar Artilces:

Why can't I use an event sink to add a header entry
The msdn site says the property is read only. Anyone know of a way to do this? ...

Form design to add data to 3 tables in "Order Entry" style db
Sorry if this is a duplicate. I thought I posted it yesterday but couldn't find it today... I need help figuring out the best way to design a form for data entry in the "Order Entry" style, adding data to three related tables. For clarity I have eliminated several fields. Here are the three tables, plus the fourth that is a reference table: tblCustomers: CustID (pk), CustName tblOrders: OrderID (pk), OrderDate, CustID (fk) tblOrderProd: OrderProdID (pk), OrderID (fk), ProdID (fk) tblProducts: ProdID (pk), ProdName Each Customer is associated with one or mo...

Combo box to find records
Hi, need help with what I am sure is a simple problem. I have a form and wish to find records using a combo box. Which I know how to do. The problem is it takes me to "one" record. I would like to select, for example, employee name: John Smith and it return all records for John Smith while filtering out all other records. I would be thankful for any help. Thanks, Michael On Wed, 30 Jan 2008 20:24:04 -0800, Michaelchessking <Michaelchessking@discussions.microsoft.com> wrote: >Hi, need help with what I am sure is a simple problem. > >I have a form and wis...

When viewing an MFC dll's dependancies can't seem to find MFC80D.d
While compiling a project that has recentlly been upgraded to VS2005 from VS6.0 i keep getting a registration error. at the end of the process the project tries to register the newly compiled DLL. when i looked at the DLL through Dependancy Walker i discovered that it could not seem to find three DLLs: MFC80D.dll, msvcp80d.dll and msvcr80d.dll. any ideas why this would happen? On Oct 20, 7:28 am, Shawn <Sh...@discussions.microsoft.com> wrote: > While compiling a project that has recentlly been upgraded to VS2005 from > VS6.0 i keep getting a registration error. at the end of ...

VBA error: Unable to set the Values property of the Series class
Excel 2003 SP1 Trying to create a chart using VBA. The following two subs are edited a bit after being created by the macro recorder. The send sub errs on this line: ActiveChart.SeriesCollection(2).Values = "=Sheet1!R12C3:R22C3" With the error: Run-time error '1004': Unable to set the Values property of the Series class Column 3 is just a series of numbers. Strangely, the first ActiveChart.SeriesCollection(1).Values line works OK, but then the 2nd fails. I can't figure it out... Sub area_chart() ' ' area_chart Macro ' ' Range("A1:C72"...

Legend
Is it possible to put in a legend in MS Project? I have colorcoded certain tasks to show that these are electrical works, and it would be nice to have a box in the corner showing Red bar: Electrical Blue bar: Civils Pink bar: Client Thanks Moeller If you are going to do this, do it in a copy of the Gantt Chart View rather than messing up the Gantt Chart View (View, More Views, Copy). Do it with a combination of Flag fields and Format, Bar Styles. Use Flag1 for Electrical, Flag2 for Civils etc. Insert the necessary three lines under the top line (Normal Tasks) in the ...

find location max value in column
I need to find the cell location of the max value in a column. If paste the following [from =(cell onwards] into a cell I get the righ result. Having a hgard time getting the right VBA code to make thi work in an unattended macro. Probably some bonehead mistake on my part but I cannot find it. Suggestions? GOAL is to make this formula work. Range("z2").Formula "=(CELL(e1,OFFSET(e1,MATCH(MAX(e1:e100),e1:100,0)-1,0)))" TIA, Ro -- rroac ----------------------------------------------------------------------- rroach's Profile: http://www.excelforum.com/member.php?act...

Finding last occurence of Interior.ColorIndex 36
I have been searching for a formula to help me find the last time (most recent) color 36 appears in a column of colored cells. Most of the time there is no typed information and when there is, it is not the same for every color 36 cell. The cells are not conditionally formatted. C. Pearson's site is great, but I can't find what I need. I don't want to change color, I don't want to know how many times it shows up, I just want to find the last time it is in the column. I could count down to find it, but there are over 15,000 columns spread over several worksheet...

upgrade to 2007 can't find exsiting website
I just upgrade from microsoft office 2003 to 2007 office enterprise, my operating system is visita. I'm trying to get my website from 2003, it comes in but none of my pictures,background,pages. doesn't show. I've uninstall the software and installe it again, hopeing this would fix the problem. I'm at a lost here. someone please help. I think Microsoft should let office 2003 run with visita, instead running out to buy some software that suppose to be compatiable. this is a waste of money. Office 2003 SP2 & SP3 will run on Vista. -- ~~~~~~~~~~~~~~~~~~ Rob Giorda...

where can i find a list of excell formula's? #2
I'm learning to use excel more and more and i would like to know how to create a formula that subtracts one cell from another. Is there a list of formula's for excel? Look in HELP contents for function index -- Don Guillett SalesAid Software donaldb@281.com "Art" <Art@discussions.microsoft.com> wrote in message news:DFF58A3A-86CE-415C-A471-0F995F95C108@microsoft.com... > I'm learning to use excel more and more and i would like to know how to > create a formula that subtracts one cell from another. Is there a list of > formula's for excel? Art, y...

how to paste a series formula across worksheets?
Version: 2004 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel <i>have a budget workbook w/12 worksheets, 1 per month. i have a row of starting totals for each month. how do i copy the starting total in january's worksheet and paste it across the following months' worksheets in increasing months? Ex: in january's worksheet, cell A2 has the total of $100. in february's worksheet, the formula in A2 is: &quot;=100+Jan!A2&quot;. how do i copy/paste the formula across the rest of the worksheets so that they read &quot;=100+Feb!A2&quot;,...

[ANN] New series of articles on using AppleScript with Entourage
I posted part 1 & 2 on Using AppleScripts with Entourage today. These articles are very basic and rated "E" for everyone and easy. Part 1 discusses the basics of using AppleScripts. Part 2 is about three of my most used scripts: Insert URLs into message Nuke Messages Insert Brackets, Quotes and Parenthesis around text If you have a favorite script you would like to share, please contact me. I can provide the script for download. <http://blog.entourage.mvps.org/> -- Diane Ross, Microsoft Mac MVP Entourage Help Page <http://www.entourage.mvps.org/> Entourage Hel...

Find 3rd Record
Hi there, I need to find the third record in a query. This is my basic select query: SELECT tblGuests.GID, tblGuests.Date FROM tblGuests ORDER BY tblGuests.GID, tblGuests.Date; There are several thousand records in tblGuests, some Guests (GID) have just one record, others have 20 or more. I've thought of DateDiff function but that doesn't seem to work. I have also thought that I need to find only records where there are more than 2 records for a certain GID so I can filter out the Guests who have only visited 1 or 2 times using the count function but have no idea how to do t...

How to generate different series of document nos for different users..
We would like GP 8.0 to auto-generate different document numbers for each user. For example: User A will be assigned INV document numbers 1-100 and User B will assigned INV document numbers 101-200 and so on. Is that possible in GP 8.0? If so, how do I go about it? Any ideas? Thanks very much in advance for you help. -Surendra It would have to be a customized solution. VBA or Dexterity are your best bets, for now. With GP 9 you will be able to use Visual Studio 2005 to create customizations, too. "Surendra" wrote: > We would like GP 8.0 to auto-generate different do...

How to find the datatype of a particular variable...
Dear pals, I am developing an win32 console application, I need to find the data type of particular variable dynamically. Is it possible? Say for eg, declaring 'a' as integer, how can I find the data type of variable 'a' dynamically. Awaiting for your ideas. note: please ignore this, if it is not a relevant group. @Shahul. I'm trying to imagine why your would need this. It sounds like this will be hard coded so you'll know. If you need something higher-level to do this, I would probably create a class that is similar to a variant in that it could store any t...

Able to find text properties?
Is there a function in excel to test the text properties of a cell. ie. if it's bold or red color text? thanks naveed010@hotmail.com G0 to the Help files, and lookup Cell worksheet function That will give you a list of the types of formats that can be returned. Mostly date, time and currency formats. The only color return would be the "negative red" in currency. What you're looking for needs code. -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ========================================...

tool to find GDI resource leak
Hallo, i search a tool or DLL or source to find GDI resource leaks. I've Boundschecker, but it don't find GDI resource leaks. Jimmy > i search a tool or DLL or source to find GDI resource leaks. > I've Boundschecker, but it don't find GDI resource leaks. Hmm, as far as I can remember BC did find GDI leaks without problems several years ago, when I was using it. You may also check this: http://msdn.microsoft.com/msdnmag/issues/03/01/GDILeaks/default.aspx -- Igor Green, Grig Software. www.grigsoft.com Compare It! + Synchronize It! : files and folders comparison neve...

Find Last instance of carriage return In a Memo Field
Hi, I have a form where the user usually pastes a glob of text into it a memo field called "Comment". After he pastes the text, the cursor is obviously at the end of the text. I would like the user to be able to click a button and for the comment to be highlighted from the very end of the text to the first instance of a carriage return going backwards. In other words, I want to automatically select the very last line of the comment. I was thinking that I could use seldown, but I would need to first find the Last instance of Chr$(13). Or, can the instr function can be ...

where can I find an excel file I "saved as" in OLKE9?
I saved over an EXCELL file that someone sent to me in OUTLOOK, after adding hours of data. Unfortunately I clicked "save as" but did not change the directory. It has saved my EXCEL FILE in a folder called OLKE9. I have searchjed the path, but cant fnd the file (I am displaying hidden files). Please help, I need this file in only 8 hours time or my life will be a misery. Easy way: Open ANY file (EXCEPT THAT ONE!!) through Outlook, and hit File-->Save as. That'll take you to the OLK folder. If you don't see it, likely, it's gone. Never, ever open files attached ...

What are these registry entries "hkcr\idid#url1", etc.
I am trying to get a handle on what "hkcr\idid#url1", "hkcr \idid#url2", etc registry locations are and what type of data they contain without success. All assistance is appreciated. Thank you in advance. Hi Allanc, Seems a Virus or Malware: Trojan.Agent/Gen HKCR\idid HKCR\idid#url1 HKCR\idid#url2 Please scan your PC with Antivirus Software in Safe Mode Regards, Jitesh Biswas http://jiteshbiswas.tripod.com "allanc" wrote: > I am trying to get a handle on what "hkcr\idid#url1", "hkcr > \idid#url2&q...

Databound form understanding problem (how to set foreign keys automatically to new foreign entries)?
Hi, I have a typical windows form (not a grid) and like to populate a foreign key automatically on a newly created entry. I have solved this by filling the new foreign data when the id field gets changed, has a valid key value and the foreign field is empty. But it takes very long time until I get the id of the newly created TableX from dataContext. Also the form gets refreshed and does not again display the lastly created TableX entry. public void Fill() { this.tableyTableAdapter.Fill(this.MyDataSet.TableY); this.tableXTableAdapter.Fi...

How To Find A Macro -- Word 2007
Our company has several hundred controlled forms that are saved as protected templates on a server. Recently, we've had many cases of people trying to open these templates and getting error messages about macros (Do you want to enable?) even though there shouldn't be any macros in the forms that are giving the errors. I know very little (read "less than nothing") about macros. How do you find and disable an unwanted macro in a document? Where do they hide? I don't know if it matters, but all of the forms that give these errors have been recently revised...

How do you set outlook calender to save previous entries
Only just realised that Outlook calender does not save previous entries older than 6 months. Is there a setting for this? Sounds like you have AutoArchive enabled, turn it off. (Tools, Options, Other....) Phil wrote: > Only just realised that Outlook calender does not save previous entries older > than 6 months. Is there a setting for this? ...

Finding Macros In Workbooks
Is there a wasy to find all Excel workbooks in a folder that have a macro in them. If so, how? Thanks, John ...

Extending XmlDocument and associated classes to provide character positions.
OK here's is what I wish to do. I have an XML file that I want to read into an XmlDocument. I then want to be able to interrogate the XmlNodes to find both their start AND end character positions within the original file. So e.g. <tagA><tagB>sometext</tagB></tagA> ^ ^ ^ ^ ^ ^ 0 6 12 19 26 33 tagA: start=0, end=33 tagB: start=6, end=26 sometext: start=12, end=19 I have seen the LineInfo example within the .net docs, see: "Extending the DOM" ms-help://MS.VSCC/MS.MSDNVS/cpguide/html/cpconextendingdom.htm and ...