What VBA Stmt Will Tell Me the Index of a Chart's SERIES Formula?

I have written a macro that allows a user to select a
specific series on an embedded chart, then specify a change
to the series limit ranges.   It works very well, EXCEPT I
have not figured out how to write a statement that will
return the index number of the selected series.  I
mistakenly believed that the series index number is the
last number on the right end of the series formula.  It's
not.  That's the plotting order sequence number, and it can
change.  I need the series index number, which I think is
unique within the workbook.  I tried using SELECTION.NAME
after selecting the series with my cursor, but about half
the time it returned the chart index.  When it succeeded,
it would return the series name, if it exists, or a value
like SERIES4, if the name does not exist.  That's too iffy.
  I need a statement that will consistently give me the
series index number.   Anyone know how to do that?
0
anonymous (74722)
6/11/2004 5:01:19 PM
excel.charting 18370 articles. 0 followers. Follow

2 Replies
761 Views

Similar Articles

[PageSpeed] 45

You have to go back in time to the land before VBA to find this one. 
Excel's old XLM was able to get some information that they left out of 
VBA. This line returns a text string with the old XLM name for the 
selected object:

   ExecuteExcel4Macro("SELECTION()")

If a series is selected, it returns "Si", where i is the series number 
you're looking for. If a single point is selected, it returns "SiPj", 
where j is the number of the point. The series are numbered in the order 
you add them to the chart, with one twist. If you make a chart with 
three series, S1, S2, and S3, then delete series 2, you are left with S1 
and S3. If you add another series now, it becomes S2, taking the slot of 
the missing series.

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

SidBord wrote:

> I have written a macro that allows a user to select a
> specific series on an embedded chart, then specify a change
> to the series limit ranges.   It works very well, EXCEPT I
> have not figured out how to write a statement that will
> return the index number of the selected series.  I
> mistakenly believed that the series index number is the
> last number on the right end of the series formula.  It's
> not.  That's the plotting order sequence number, and it can
> change.  I need the series index number, which I think is
> unique within the workbook.  I tried using SELECTION.NAME
> after selecting the series with my cursor, but about half
> the time it returned the chart index.  When it succeeded,
> it would return the series name, if it exists, or a value
> like SERIES4, if the name does not exist.  That's too iffy.
>   I need a statement that will consistently give me the
> series index number.   Anyone know how to do that?

0
DOjonNOT (619)
6/12/2004 10:37:57 PM
Why do you want the series's index number?

Is it so you can access the series object like this...

Activechart.seriescollection(i)

Maybe an alternative to the excel4macro would be to use a function tha
loops through the Seriescollection and compares the .Formula t
Selection.Formula.  When you get a match, you can be darn sure you'v
got the index number of the selected series.  The .Formula propert
should be unique, so this approach ought to work under all conditions.
But watch out for items in the SeriesCollection that don't return 
.Formula property (like when the charted cell ranges are empty).

Brian Murphy
Austin, Texa

--
Message posted from http://www.ExcelForum.com

0
6/23/2004 6:26:00 PM
Reply:

Similar Artilces:

Series Posting #2
As I try to use the Transactions => Financial => Series Posting and try to post the batches, i get the following message: A get/change operation on table 'SY_posting_journal_destination' cannot find the table i don't know if this is affecting my database in any way, does anyone have a solution for this problem? Thank You, Fadi Tawakol Fadi, You can try opening SQL Server Management Studio and search for the SY02200 table in your company database. If it's not there, then... it's gone and you will need to recreate it. To recreate the table, go to Microsoft Dyn...

How to apply a formula accross all the rows when the total rows are too much in no. ?
Hi, guyz i know you can drag the formula accross the rows in that column t have that formula in effect in every cell. but i have some data lik 5000 rows then how to get that formula accross each cell, its painfu holding the mouse and dragging the formula accross the rows. please advise thanks in advance sorab -- Message posted from http://www.ExcelForum.com Hi after inserting this formula in the first row double click on the lower right corner of your cell selection -- Regards Frank Kabel Frankfurt, Germany > Hi, > guyz i know you can drag the formula accross the rows in that col...

Workbook Window will not maximize
Help! I am using Excel 2007 SP2 on Windows XP. When I open my Workbook, the program window opens maximized, but the workbook window opens smaller and the workbook window does not have the maximize, minimize or close buttons like usual. I can't figure out how to fix this. Has anyone else dealt with this before? On Nov 18, 9:06=A0am, magmike <magmi...@yahoo.com> wrote: > Help! I am using Excel 2007 SP2 on Windows XP. > > When I open my Workbook, the program window opens maximized, but the > workbook window opens smaller and the workbook window does not have > the maxim...

Outlook will not run an start up as a scheduled task
I have set up Outlook 2003 as a schduled task to run at start up. It doesn't run because "the scheduled task did not run because an incorrect password or user name was entered". I have not created any password in Outlook. Any ideas? Pete Outlook is not suitable to run from the Windows task scheduler. If you want it to run at Windows startup, add a shortcut to Outlook.exe to the Programs | Startup group. -- Sue Mosher, Outlook MVP Author of Microsoft Outlook Programming - Jumpstart for Administrators, Power Users, and Developers http://www.outlookcode.com...

Automatic Charts in Excel
Hello !!! I have an external program that generates data files in CSV format Column A contains my data and Column E contain time of day values i 24hr:min:sec format (22:12:30) The CSV gets updated every 10 second and no 2 rows have the same time value. After the CSV is closed I nee to do the following... Open a worksheet (excel 2000) with a macro already loaded. Run the macro which prompts the user to load a CSV file. Search column E for the time value 22:00:00 If not found then select E1 Search column E for the time value 04:00:00 If not found select the last cell in column E Create a range...

help choosing a chart format.
In my last job, we had a waterfall plot that would show time on the X axis, frequency on the y axes and the marker would change color according to magnitude of the signal. is there a good chart that will perform this type of plot? It is kind of like an XY scatter plot with a variable color. see peltiertech.com/Excel/Charts/Waterfall.html best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "fugazi48" <fugazi48@discussions.microsoft.com> wrote in message news:6A6088FA-C207-4CB2-A103-02CD9549D7E5@microsoft.com... > In my last job, we ...

VBA code not working on other PC
I am using Excel 2003 and I have been using the VBA code below on my computer for a long time and find it extremely useful and which has never result in an error. A colleague asked if he could also have in on his computer and so I copied it over. The strange thing is it doesn't seem to work on his computer and I not sure why. I stops on the line 'Dim Result As New DataObject' that I have higlighted with the *********. and a dialogue box appears saying: Compile Error: User defined type not defined. Does anyone know the answer why it works on my PC and not on his? I...

EXCEL chart legend formatting
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Can anyone tell me how to get superscript/subscript formatting to show in a chart legend? I've got it working in the excel data cells, but the formattin doesn't transfer to the legend and I can't directly change the legend text. I am having the same problem. Did you ever find a solution? Thanks! tauntonuse wrote: EXCEL chart legend formatting 28-Oct-09 Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Can anyone tell me how to get superscript/subscript formatting to show in a chart legend? I h...

Exporting an Excel Chart into Access
Hi, Does anyone know how to basically do as the title of this post says: Export an Excel Chart into a report and/or form on Access? I know I will be pushing my luck here, but if anyone also knows if it is possible to produce a graph from a query in Access - please let me know! Thanks, T8RSP -- T8RSP ------------------------------------------------------------------------ T8RSP's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=28452 View this thread: http://www.excelforum.com/showthread.php?threadid=517792 ...

Reduce chart white space
When I create a pie chart, a terrible amount of white space exists around the pie. When I insert it into PowerPoint, it takes up way too much room. I can increase the size of the pie, but that still leaves a tremendous amount of white space on the left and right sides. Any ideas? Thanks, Vinod Crop the object in PowerPoint ... After you paste, Format Picture / Picture Tab allows you to crop the item. >-----Original Message----- >When I create a pie chart, a terrible amount of white space exists >around the pie. When I insert it into PowerPoint, it takes up way too >much ro...

Need Help with Coding a ComboBox in Excel/VBa......
Hi all, Perhaps someone can help me.......... I have the following code written in VBA. Private Sub SupervisorComboBox_Change() Sheets("Sheet2").Range("C1") = SupervisorComboBox.Value I have a ComboBox that lists ten supervisors. When the user chooses a supervisor from the ComboBox drop down list the name gets stored on the second Sheet, in cell, "C1". Now then.......there is a second ComboBox underneath this first one This ComboBox lists each employee that works under the chose supervisor. The employee's name gets stored in cell, "D1" My que...

formula #59
i am trying to do this:: =IF(J48<25,"",K48) just insert the info from I48 into K48 BUT =IF(J48>25) then add J48 info to I48 and place the total in K48. is this possible and how is it done =if(j48<25,"",if(j48>25,j48+i48)) -- y_not ------------------------------------------------------------------------ y_not's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=19947 View this thread: http://www.excelforum.com/showthread.php?threadid=394739 A formula can only enter values into the cell in which it resides, it cannot enter data int...

Naming a Chart
Excel 2000 Power Programming with VBA states that naming an object requires selecting the object (such as a chart), typing the new name in the Name Box, and pressing Enter. This does work for a shape but not a chart. When I select a chart and attempt to enter anything in the Name Box, any entry is not enabled and therefore nothing happens. How can I assign a name to a chart??? Hold the Shift key (or the Ctrl key), and click on the chart to select it. Then, type a name in the Name Box, and press the Enter key. William Bartusek wrote: > Excel 2000 Power Programming with VBA states tha...

Chart Information
I'm using AC2003. I have a chart on a form. Sometimes the chart has no data and the chart displayed is blank, which I expect. My question is, is there a way to determine whether the chart has any information on it? I'd like to display a label that says something like "No Information Found". Thanks in advance for your help. A chart has a row source and maybe a link master/child properties. You should be able to use Dcount() or something to determine if there are any records. -- Duane Hookom MS Access MVP "PeterM" <PeterM@discussions...

full text indexing
Every table needs to be created so they can be full text indexed enhancing the search capability. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/Businesssolutions/Community/NewsGroups/dgbrowser/en-us/default.mspx?mid=b3051f89-4188-4427-9817-7e...

copying charts into new sheets, data is pulled from old sheet
i make a sheet with a load of data and some graphs, then i want to copy the whole thing into a new sheet to process some similar but different data, all the cell references translate cleanly, and refer to the new sheet however, the graphs reflec tthe previous sheets ranges and data. how can i copy the graphs so they refer to cells on the new sheet? btw i know excel pretty well, and i could go through and drag new data on, but i would like about 30 graphs per sheet, so its tedious. thanks instead of copying just the data, use the Move or Copy Sheet option under Edit and copy the whol...

Using a named range in a chart
Can a named range be used in a chart for one of the series? I have tried this, and it doesn't seem to work. For instance, i have a name _Date_rng defined (using Insert...Name...Define) as "data!$A$2:$A$60". In a chart (XY scatterplot), for one series, i define the X values as _Date_rng. Excel formats this as ={"_Date_rng"}. However, the chart is now blank, it draws with the axes, but no data is plotted. But, when i put the range directly in the chart "Source Data...Series" tab as "data!$A$2:$A$60", it plots the data correctly. So, is it po...

CEdit will not accept text. What do I do?
Here is the scenario. I have a dialog which owns a popup window which owns a cedit control. When I set the focus to the edit control the caret blinks in the control and I can use the following keys: Insert, delete, home, end, page up, page down, and the arrow keys. I can't type any character into the control. I can highlight text and delete it, I can right click and paste into the control, but when I hit a letter, number or symbol key nothing happens. I have placed a break point in the control's OnKeyUp and OnKeyDown functions and the points are hit. I have even tried calling OnChar fr...

orden en los operadores de las formulas
deseo saber cual es el orden correcto para generar formulas en una hoja de excel -- Excel "angies" <angiemarbella27@hotmail.com> wrote in message news:E03784BA-F3E8-4E54-84EA-00D151DD939A@microsoft.com... > deseo saber cual es el orden correcto para generar formulas en una hoja de > excel > -- > Excel La multiplicación y la división antes de la adición y de la substracción. Por ejemplo, si usted escribe =5+2*3, el resultado será 11. Si quiere 21 por el resultado , tiene que usar parentesis: =(5+2)*3 ...

Adding Comments From Cells To Chart Points.
Hi all I have spent a large portion of my weekend trying to find the answer to this. Without much luck! I have finally come up with my own code and was hoping someone could have a look at it. I'm looking to clean it up and make it a little more efficient. Thanks in advance, Greg. Sub AddCommentsToChartPoints() Dim ws As Worksheet Dim ct As ChartObject Dim serSeries As SeriesCollection Dim ser As Series Dim Counter As Integer Dim ChartName As String Dim xVals As String Dim xAddress As String 'Loop through each worksheet in workbook For Each ws In Worksheets 'Lo...

multiple indexes in one document
I have a document that lists information in a table. This information has to be split into two indexes in separate parts of the same document. Trick is that one index will need to contain all of the information and the other just certain pieces. I created the main index and it is fine, however I do not know how to double mark entries for use in the second index. There are two separate mechanisms for this, and you can use one or the other. (I would discourage trying to use both at the same time.) If all the items for the second index are together in one part of the table...

Discounting null entries from Pie & Bar Chart
Hello all, I've got a simple and colourful Worksheet that updates itself live depending on data that is pasted into sheet 2 of it. The thing is, when people are all in it's fine, everything is plotted correctly(http://hayn.gotadsl.co.uk/excelfull.jpg), but when several people are absent it munches them all together. Notice the circled segment on this screenshot below: http://hayn.gotadsl.co.uk/excel.jpg How can I stop that? I just want it not to plot the absent people. --Nick. -- http://www.sendit.com/scp/id/what - Want DVDs/Games? icq: 9235201 -- Hayn on dal.net - Mmmm... choc...

Formulas not calculating #2
Hello, i avea quick question. i have formulas in cells. such as this in cell C1: =IF(A1="","",A1&"/ "&B1) but when i put a value in A1 it does not evaluate. i have to go into C1 and hit enter. then it evaluates. is there a setting in excel that i need to set? thanks, Hi goto 'Tools - Options - Calculate' and ebale 'Automatic Calculation' -- Regards Frank Kabel Frankfurt, Germany greg wrote: > Hello, > i avea quick question. > i have formulas in cells. such as this in cell C1: > =IF(A1="","",A1&...

Full-Text Indexing #2
I have Exchange 2003 and am running a cluster with 1 Exchange virtual server. When i try to create a full-text index on a mailbox store i get the following error "the rpc server is unavailable id no:800706b that's it. any ideas thanks ...

VBA-recovering a broken link
Hi everyone, Lets assume we copied a range from excel and pasted it as a link in Word or PowerPoint , and we have the exact link string . Is it possible in VBA to recover the link using that string after we remove the link ? What I'm trying to do is to paste a range as a link then save the path and remove the link and whenever I need that specific object to be updated I restablish a link between target and source . I can easily do all the sateps in VBA except for the last part that I don't know how to establish a link between source and target using the the path . Any s...