+ Reply to Thread
Results 1 to 11 of 11

Interactive Chart Display

  1. #1
    Registered User
    Join Date
    08-25-2010
    Location
    Chicago
    MS-Off Ver
    Excel 2003
    Posts
    6

    Cool Interactive Chart Display

    I am trying to build a chart (and actually I want this to apply for all embedded charts on a certain sheet) that displays information in adjacent columns when I mouse down on a data point. Right, now I'm using a MsgBox to display xvalues and values. Ideally, when I click on point (x,y1) I will get a message with the point values and also the value of (x,a) [I do not want column a to be shown on the chart]. When I click on point (x,y2) [y2 is a series shown on the chart] I want to see the point value as well as the info in (x,b). Here is an example of how I have my data set up. [note: the headings are shifted. Column b is 68, 64, ... 59.] How do I do this?

    x y1 a y2 b
    500 30 88 32 68
    550 31 90 33 64
    600 32 72 34 101
    610 33 139 35 137
    620 34 134 36 59
    Last edited by exce; 08-27-2010 at 05:09 AM.

  2. #2
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: Interactive Chart Display

    Post a workbook with your data and what you have achieved so far. It is not quite clear what you want to do, or where the data is. The forum software is not best suited to display tabular data. That's where spreadsheets are really handy.

  3. #3
    Registered User
    Join Date
    08-25-2010
    Location
    Chicago
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: Interactive Chart Display

    Ok here's the file. The x-values for the chart are column A. Two series of values are plotted: cpb and cpa. When I click on a data point in one of these series I get a msgbox with the x-value and the value of the series. I also want the corresponding entry for bsz (when I click a cpb point) or asz (when I click a cpa point). What I have isn't working; specifically the
    & "Sz=" & rngData.Cells(Arg2, Arg1)
    part of

    MsgBox """" & .SeriesCollection(Arg1).Name & """" & vbCrLf _
    & "S = " & myX & vbCrLf _
    & "V = " & myY & vbCrLf _
    & "Sz=" & rngData.Cells(Arg2, Arg1)
    Also I would like to be to add more columns later on between cpb and cpa so that when I click a point from cpb I see the corresponding data of the new column.

    [the graph is on sheet2]
    Attached Files Attached Files

  4. #4
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,434

    Re: Interactive Chart Display

    Please Login or Register  to view this content.
    Cheers
    Andy
    www.andypope.info

  5. #5
    Registered User
    Join Date
    08-25-2010
    Location
    Chicago
    MS-Off Ver
    Excel 2003
    Posts
    6

    Question Re: Interactive Chart Display

    ...Well, that's not quite it. I've attached another excel file, so we can attack a simpler version.
    This file has only one data series on the chart (I'll add more later). When I click on a data point, I would like to see a message box with the x-values, values, and the bsz value. So, for example, when I click on the first point I would like to see a box that says "500, 30, 41." So far I can only get "500, 30." How do I display data from column C (bsz) in the msgBox also?
    Attached Files Attached Files

  6. #6
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,434

    Re: Interactive Chart Display

    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    08-25-2010
    Location
    Chicago
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: Interactive Chart Display

    Ok, but I want "bsz" to always reference the column directly to the right of "V". I want to be able to add another series to the chart and get the corresponding info. Do I need a variable instead of "3" in "bsz=" & rngData.Cells(Arg2, 3))?

  8. #8
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,434

    Re: Interactive Chart Display

    Have a look at John Walkenbach's class code which extract the data range from a series formula.
    http://spreadsheetpage.com/index.php..._chart_series/

    You can then use that range and the Offset property to get at any information relative to the data you need.

  9. #9
    Registered User
    Join Date
    08-25-2010
    Location
    Chicago
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: Interactive Chart Display

    ...Well, I've spent some time with it and admittedly im not quite sure which part extracts the data range. I think I know how to use offset, but it's the first step i'm stuck on.

  10. #10
    Registered User
    Join Date
    08-25-2010
    Location
    Chicago
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: Interactive Chart Display

    Ok. I figured out an alternative. But one question remains: Is there a way to extract the row information for a data point? So that if, say, I click the third point in series one, I get a message with the row number of the data info. Note: the row number will not (in general) be the point number, so just returning arg2 (I have learned) will not work.

  11. #11
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,434

    Re: Interactive Chart Display

    In the link I gave you you should be able to extract the data range.
    You can then use the Arg2 value relative to the data range.

    So assuming you have an object referencing the values in a data series forumla then something like this should work

    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1